SQL：分组排序取top N

1 法一

在hive上查询

select a.course,a.score
from(
select course,score,row_number() over(partition by course order by score desc) as n
from lesson
) a
where a.n<=2; 

其中：row_number() over(partition by course order by score desc)意思是以课程分组，按成绩递减排序，并为每组中的数据打上行号的标记，从1开始。这样，再在外层套一层过滤行号小于等于2的即可。

2 法二

在一张表里有班级、成绩、学生姓名、id，现在要求获取每个班级里成绩前2的学生信息， 在这张表中有好几个班级，所以你有可能首先想到的是group by分组，但group by分组后取出来的数据只有一条，因此肯定不能用group by。
我们不妨换一种思路，我们可不可以对比成绩，拿班级里一个学生的成绩和班级里其他学生成绩对比，然后统计出分数比他高的人数，最后只需通过这个统计的人数来获取班级前2的学生信息记录。

通过上面两张表可以看出，如果想要获取每个班级前2的学习记录，只需查询成绩比他高的人数小于2的学生就可以了。

select * from grade g1 
where (
select count(*) from grade g2 where g2.class=g1.class and g2.score>g1.score
)<2 order by g1.class asc,g1.score desc

这句查询语句的意思是：查询整个表的数据，在查询过程中统计出班里成绩比他高的人数，然后筛选出这个统计人数小于2的学生记录。其实，不要后面的 order by 也能查出我们想要的结果，只不过结果比较混乱。而加上 order by排序是让查询结果以班级排序，然后每个班级里又按成绩降序排序，让查询结果一目了然。

通过上面的获取每个班级前 2 的学生记录，我们同样可以获取排名倒数 2 名的学生记录：

select * from grade g1 
where(
select count(*) from grade g2 where g2.class=g1.class and g2.score<g1.score 
)<2 order by g1.class asc, g1.score asc

• [SQL] 经典topN问题：每组最大的N条记录
• mysql实现分组查询取每组前几的记录思路解析