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;