mysql8 中的rank_Mysql8.0+中的rank()、row_num()、dense_rank()等窗口函数

mysql8 中的rank_Mysql8.0+中的rank()、row_num()、dense_rank()等窗口函数

2024-12-27 09:13

在低于Mysql8.0之前的版本中，要对序列进行排序的话需要用到自定义参数@，但是8.0+版本中加入了oracle中的窗口函数，rank()、row_num()、dense_rank()等函数。

下面简单举几个例子介绍下这几个函数的应用。测试用的表主要有两个，一个是学生信息表student，二是学生分数表sc，具体的测试数据的mysql插入语句在另一篇博客：Mysql经典练习题与知识点总结中，大家可以在Mysql中试着插入并测试。但Mysql的版本要高于8.0，具体安装过程可以在其他博客中找一下。

row_number()

它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

row_num() over (order by字段1)以字段1排序，生成行号

rank()

用于返回结果集的分区内每行的排名，行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。

rank() over (partition by字段1 order by 字段2)

按字段1分组并按字段2倒序排序，输出结果。partition by非必要，order by是必要的。

partition by非必要，order by必要

dense_rank()

与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。

dense_rank() over (partition by字段1 order by 字段2)

关于partition by和group by的区别

partition by一般用于over字句中，group by顺序正常

partition by会保留所有的记录，并依据order by的结果依次输出，而group by则通常与聚合函数一起使用，只会返回聚合后的唯一记录。

student表

+------+-------+---------------------+------+

+------+-------+---------------------+------+

| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |

| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |

| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |

| 04 | 李云 | 1990-08-06 00:00:00 | 男 |

| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |

| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |

| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |

| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |

+------+-------+---------------------+------+

8 rows in set (0.00 sec)

sc表

+------+------+-------+

| Sid | Cid | score |

+------+------+-------+

| 01 | 01 | 80.0 |

| 01 | 02 | 90.0 |

| 01 | 03 | 99.0 |

| 02 | 01 | 70.0 |

| 02 | 02 | 60.0 |

| 02 | 03 | 80.0 |

| 03 | 01 | 80.0 |

| 03 | 02 | 80.0 |

| 03 | 03 | 80.0 |

| 04 | 01 | 50.0 |

| 04 | 02 | 30.0 |

| 04 | 03 | 20.0 |

| 05 | 01 | 76.0 |

| 05 | 02 | 87.0 |

| 06 | 01 | 31.0 |

| 06 | 03 | 34.0 |

| 07 | 02 | 89.0 |

| 07 | 03 | 98.0 |

+------+------+-------+

18 rows in set (0.00 sec)

rank()和dense_rank()

1. 对学生总成绩从大到小排序，并查询学生信息，没有成绩的也显示。

SELECT

s.*, rank () over (ORDER BY sum(sc.score) DESC) AS rank_01

FROM

student s LEFT JOIN

s.Sid = sc.Sid

GROUP BY

sc.Sid;

结果

+------+-------+---------------------+------+---------+

+------+-------+---------------------+------+---------+

| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 1 |

| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 2 |

| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 3 |

| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 4 |

| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 5 |

| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 6 |

| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 7 |

| 08 | 王菊 | 1990-01-20 00:00:00 | 女 | 8 |

+------+-------+---------------------+------+---------+

如果见到null的情况下要显示，则需要left join

rank() over(partition by 字段1 order by字段2 desc) 按字段1分组并按字段2倒序排序，输出结果。partition by非必要，order by是必要的。

2、查询各科成绩前三名的记录

SELECT * FROM

(SELECT *, rank () over (PARTITION BY Cid ORDER BY score DESC) AS grade

FROM sc) AS t

WHERE t.grade <= 3;

+------+------+-------+-------+

| Sid | Cid | score | grade |

+------+------+-------+-------+

| 01 | 01 | 80.0 | 1 |

| 03 | 01 | 80.0 | 1 |

| 05 | 01 | 76.0 | 3 |

| 01 | 02 | 90.0 | 1 |

| 07 | 02 | 89.0 | 2 |

| 05 | 02 | 87.0 | 3 |

| 01 | 03 | 99.0 | 1 |

| 07 | 03 | 98.0 | 2 |

| 02 | 03 | 80.0 | 3 |

| 03 | 03 | 80.0 | 3 |

+------+------+-------+-------+

10 rows in set (0.00 sec)

如果要对rank的结果进行进一步选择的话，采用结构select * from (select rank()) where 的句式