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