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