mysql-sql语句-获取排名（包括是否并列排名）

举例数据表

如库中有表scores，表中已有数据如下：

score排名前3的记录

方法1. select * from scores order by score desc limit 3
注：不支持并列排名的情况

方法2. select * from scores where score>= (select max(score) from scores where score <(select max(score) from scores where score not in (select max(score) from scores))) order by score desc
注：支持并列排名的情况

score排名倒数3名的记录

方法1. select * from scores order by score limit 3
注：不支持并列排名的情况

方法2. select * from scores where score<= (select min(score) from scores where score >(select min(score) from scores where score not in (select min(score) from scores))) order by score
注：支持并列排名的情况

score排名第2的记录

方法1. select * from scores order by score desc limit 1,1
注：不支持并列排名的情况

方法2. select * from scores where score = (select max(score) from scores where score <(select max(score) from scores))
注：支持并列排名的情况

方法3. select * from scores where score in (select max(score) from scores where score not in (select max(score) from scores))
注：支持并列排名的情况

score排名倒数第3的记录

方法1. select * from scores order by score limit 2,1
注：不支持并列排名的情况

方法2. select * from scores where score= (select min(score) from scores where score >(select min(score) from scores where score not in (select min(score) from scores)))
注：支持并列排名的情况

score排名前2-3名的记录

方法1. select * from scores order by score desc limit 1,2
注：
1.不支持并列排名的情况
2.此语句语法为：select [fields] from [table] order by [field] [desc] limit 偏移量（从0开始），行数

方法2: select * from scores where score>= (select max(score) from scores where score <(select max(score) from scores where score not in (select max(score) from scores))) and score< (select max(score) from scores) order by score desc
注：支持并列排名的情况

score排名前3和后3的记录

select * from(select * from scores order by score limit 3) s1
union all
select * from (select * from scores order by score desc limit 3) s2

注：
1.一定要有别名，否则会报错（我猜是因为from后必须要跟表名，而直接select出来的数据不认为是表的一部分数据，因此为其起个名字作为表进行数据查询）
2.order by和union冲突，mysql不支持直接用
3.union 是会去掉重复的数据，而 union all则不去重
4.不支持并列排名情况，可以套用前2个例子的sql语句，将其union

如有下表class_scores，数据如下：

每个班级排名前2的记录

方法： select * from class_scores a where (select count(*) from class_scores b where a.class = b.class and a.score<b.score)<2 order by a.class,a.score desc
注：支持并列排名的情况

每个班级排名倒数第1的记录

方法： select * from class_scores a where (select count(*) from class_scores b where a.class = b.class and a.score>b.score)<1 order by a.class
注：支持并列排名的情况

每个班级排名第2的记录

方法: select * from class_scores a where (select count() from class_scores b where a.class = b.class and a.score<b.score)<2 and (select count() from class_scores b where a.class = b.class and a.score<b.score)>=1 order by a.class,a.score desc

注：
1.支持并列排名的情况
2. mysql不支持子查询中使用limit

MySQL中实现rank排名查询

看其他同学已经有一篇文章，总结的不错，有需要的可以看下。地址是：MySQL中实现rank排名查询