SQL topN相关问题

sql topN相关

比如说：不同班级同学成绩的前三名同学；

先来一个前置知识的介绍；

这个是一个班级的成绩表，表名为study, 班级字段为class 姓名字段为name 成绩字段为score;首先，难点在于每个课程前3高成绩。前三高的成绩，意味着我们需要对成绩进行排名。这个时候，就需要使用到窗口函数了。

select *,
   rank() over (order by score desc) as ranking,
   dense_rank() over (order by score desc) as dese_rank,
   row_number() over (order by score desc) as row_num
from study;

很显然，这里是对成绩来进行排名；但是，这三种排名有自己的差异；

第一种：比如说：两个人考了90 一个人考了80分 那么 90分的这两个人都是排名第1 80分的这个人排名第3；
第二种，以上面为例子，就是两个90分的排名第1，80分的同学排名第2；
第三种，前两个同学也按照顺序来进行排列，分别排第一 和 第二，具体怎么排，通过name的顺序啊什么的，第三个同学排名第3；
解决完排名问题以后，就需要解决按照课程号来进行排名了 ： 这个时候，就使用到了partition by 课程号来进行分割了（partition – 分割）

我们，已经可以对score来进行等级排序了，那么，我们就需要再对其进行分割；就是 每个班级的同学需要在一起；

select *,
   row_number() over (partition by study.class order by study.score desc) as row_num
   FROM study

由于，加入了partition by class 所以，它会根据班级来进行排序；如下图所以：

这个时候，我们就需要从这个找出来的表中，再进行筛选；

select * from (找出来的数据) where 条件； 来进行分割

select * from (select *,
   row_number() over (partition by study.class order by study.score desc) as row_num
	 
	 FROM study
	 ) as test where row_num <= 3;

这时候，就找到每个班级的前三名了；
其实，这里就是使用了窗口函数，使其可以按班级来进行排序，自己的话，再根据它的排序结果，进行where 筛选符合条件的要求即可；
备注：

这里，一定要把他当作一张新表；

补充一下：这个窗口函数partition by 是可以分割多个的，比如说：刚刚的例子是取出每个班的前三，但是，如果你取的是每个班每门课的前三名也是可以的。代码如下：

# 不同的例子 表结构是不同的
select * from (select *,
   row_number() over (partition by student.class, student.`subject` order by student.score desc) as row_num
   FROM student) as test where row_num <= 3;