mysql成绩排名
关于mysql成绩排名,网上大部分只是order by简单排序,忽略了成绩相同并列名次的问题。
定义了一个表score结构为:
mysql随数据库中意添加一些数据。
SELECT * FROM score;
查询结果为:
我的解决方案:
SET @score = 0; SET @sort = 1; SET @num = 0; SELECT id, stundetNo, score, sort FROM ( SELECT id, stundetNo, score, @num: = @num + 1, IF (score = @score, @sort, @sort: = @num) AS sort, @score: = score FROM score ORDER BY score DESC ) AS t;
结果为:
或者:
SELECT id, stundetNo, score, ( SELECT count(*) + 1 FROM score s WHERE s.score > score.score ) sort FROM score
结果为: