怎么用MySQL窗口函数实现榜单排名

怎么用MySQL窗口函数实现榜单排名

首先，先建一个测试表

create table praise_record( id bigint primary key auto_increment, name varchar(10), praise_num int) ENGINE=InnoDB;登录后复制

然后让chatGpt给我们生成几条测试数据

INSERT INTO praise_record (name, praise_num) VALUES ('John', 5);INSERT INTO praise_record (name, praise_num) VALUES ('Jane', 3);INSERT INTO praise_record (name, praise_num) VALUES ('Bob', 10);INSERT INTO praise_record (name, praise_num) VALUES ('Alice', 3);INSERT INTO praise_record (name, praise_num) VALUES ('David', 7);INSERT INTO praise_record (name, praise_num) VALUES ('oct', 7);登录后复制

然后就可以开始实现我们的需求：返回点赞的榜单，并返回排名

rank()

使用rank()函数返回点赞的榜单， rank() over()

## 注意这里返回的rank字段要用反引号包起来select name, praise_num, rank() over (order by praise_num desc) as `rank` from praise_record;+-------+------------+------+| name | praise_num | rank |+-------+------------+------+| Bob | 10 | 1 || David | 7 | 2 || oct | 7 | 2 || John | 5 | 4 || Jane | 3 | 5 || Alice | 3 | 5 |+-------+------------+------+登录后复制

当使用rank()函数时，相同的点赞数会得到相同的排名，排名可能就会产生跳跃现象，所以最终的排名不会是连续的

dense_rank()

使用dense_rank()函数返回点赞的榜单， dense_rank() over()

select name, praise_num, dense_rank() over (order by praise_num desc) as `rank` from praise_record; +-------+------------+------+| name | praise_num | rank |+-------+------------+------+| Bob | 10 | 1 || David | 7 | 2 || oct | 7 | 2 || John | 5 | 3 || Jane | 3 | 4 || Alice | 3 | 4 |+-------+------------+------+登录后复制

与rank()函数相同的是，相同点赞数会返回相同的排名，但是dense_rank()返回的最终排名是连续的排名

row_number()

row_number()函数返回点赞的榜单，row_number() over()

select name, praise_num, row_number() over (order by praise_num desc) as `rank` from praise_record; +-------+------------+------+| name | praise_num | rank |+-------+------------+------+| Bob | 10 | 1 || David | 7 | 2 || oct | 7 | 3 || John | 5 | 4 || Jane | 3 | 5 || Alice | 3 | 6 |+-------+------------+------+登录后复制

row_number()函数适合当返回的列表只需要序号时使用

以上三个函数都是MySQL8.0新加入的，所以在MySQL5.7这些老版本上我们可以模拟实现一下，顺便学习一下这三个窗口函数的实现原理

rank()函数的模拟实现

select p1.name, p1.praise_num, count(p2.praise_num) + 1 as `rank` from praise_record p1left join praise_record p2 on p1.praise_num < p2.praise_numgroup by p1.name, p1.praise_numorder by `rank`;+-------+------------+------+| name | praise_num | rank |+-------+------------+------+| Bob | 10 | 1 || David | 7 | 2 || oct | 7 | 2 || John | 5 | 4 || Jane | 3 | 5 || Alice | 3 | 5 |+-------+------------+------+登录后复制

我们可以使用自联接的方式将每个分数低于当前行分数的记录计数，最后将计数值加1作为当前行的排名，来模拟实现rank()

dense_rank()的模拟实现

select p1.name, p1.praise_num, count(distinct p2.praise_num) + 1 as `dense_rank` from praise_record p1left join praise_record p2 on p1.praise_num < p2.praise_numgroup by p1.name, p1.praise_numorder by `dense_rank`;+-------+------------+------------+| name | praise_num | dense_rank |+-------+------------+------------+| Bob | 10 | 1 || oct | 7 | 2 || David | 7 | 2 || John | 5 | 3 || Jane | 3 | 4 || Alice | 3 | 4 |+-------+------------+------------+登录后复制

dense_rank的实现与rank差不多，唯一的区别是增加了distinct对点赞数做了去重，这样子对不同的点赞数返回的排名就是连续的

row_number的模拟实现

##使用自定义变量得先初始化set @rowNum = 0;select name, praise_num, @rowNum := @rowNum +1 as `row_number` from praise_record order by praise_num desc ;+-------+------------+------------+| name | praise_num | row_number |+-------+------------+------------+| Bob | 10 | 1 || David | 7 | 2 || oct | 7 | 3 || John | 5 | 4 || Jane | 3 | 5 || Alice | 3 | 6 |+-------+------------+------------+登录后复制

我们可以使用一个rowNum变量来记录行号，每一行的数据rowNUm都+1，这样子就可以得到我们想要的序号

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。