类似查询一个班的成绩,取出前三名

用成绩表来举例

假设有这样一张成绩表,分数有重复,按分数取出前三名并排序

select s1.name 姓名,s1.score 分数,(select count(distinct s2.score) from `students` s2 where s2.score>s1.score)+1 as 排名 from `students` s1 
where s1.score >= (select min(a.score) from (select distinct score from `students` order by score desc limit 3)a) order by 排名; 

将count(distinct s2.score)改成count(1) 就是跳过位次的顺序

select s1.name 姓名,s1.score 分数,(select count(1) from `students` s2 where s2.score>s1.score)+1 as 排名 from `students` s1 
where s1.score >= (select min(a.score) from (select distinct score from `students` order by score desc limit 3)a) order by 排名;