mysql - SQL 将结果限制为前 50%

Question

我尝试了两种方法，但在mysql中均失败。

/*see top 50% students, but this sql can't work*/
select * from student_table order by chinese_score desc limit count(*) * 0.5 ;

/*also can't work*/
set @num= floor((select count(*) from test.student_score)*0.5);
select * from student_table order by chinese_score desc limit @num ;

如何在mysql中解决？

Answer 1

在 Mysql 中，这可以使用 user defined variables 在单个查询中完成.

You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another.

SELECT * FROM    (
          SELECT student_table.*, @counter := @counter +1 AS counter
          FROM (SELECT @counter:=0) AS initvar, student_table
          ORDER BY student_table.chinese_score DESC
        ) AS result
WHERE counter < (@counter/2) ORDER BY chinese_score DESC;