Mysql 窗口函数

专用窗口函数rank, dense_rank, row_number

排序问题:

例:
题目:下图是"班级"表中的内容,记录了每个学生所在班级,和对应的成绩。
在这里插入图片描述

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级

得到结果:
在这里插入图片描述
从上面的结果可以看出:
1)rank函数:这个例子中是5位,5位,5位,8位,也就是如果有并列名次的行,会占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,4。

2)dense_rank函数:这个例子中是5位,5位,5位,6位,也就是如果有并列名次的行,不占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,2。

3)row_number函数:这个例子中是5位,6位,7位,8位,也就是不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1,2,3,4。

这三个函数的区别如下:
在这里插入图片描述

TOP N 问题

每组最大的N条记录(topN)

例:
题目:现有“成绩表”,记录了每个学生各科的成绩。表内容如下:
在这里插入图片描述
问题:查找每个学生成绩最高的2个科目

【解题思路】

1.看到问题中要查“每个”学生最高的成绩。还记得我们之前课程里讲过的吗?当有“每个”出现的时候,就要想到是要分组了。
这里是“每个学生”,结合表的结构,是按学生“姓名”来分组。

2.将表按学生姓名分组后,把成绩按降序排列,排在最前面的2个就是我们要找的“成绩最高的2个科目”。

3.现在分组后,需要排序,又不减少原表的行数,这种功能自然想到是窗口函数。

4.使用哪个专用窗口函数?
为了不受并列成绩的影响,使用row_number专用窗口函数;

常见报错写法:

select *, 
      row_number() over (partition by 姓名
                   order by 成绩 desc) as ranking
from 成绩表
where ranking <=2

原因:
在这里插入图片描述
运行到”where ranking > 2”的时候,因为select字句还没有被执行,因此select中的“ranking”列还没有出现,从而导致报错。

解决方法:这种情况就可以用子查询,也就是把第一步得到查询结果作为一个新的表

select *
from (
   select *, 
          row_number() over (partition by 姓名
                       order by 成绩 desc) as ranking
   from 成绩表) as a
where ranking <=2

可以归纳出TOP N问题的解决模板

# topN问题 sql模板
select *
from (
   select *, 
          row_number() over (partition by 要分组的列名
                       order by 要排序的列名 desc) as ranking
   from 表名) as a
where ranking <= N
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页