本篇内容主要讲解“怎么用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() 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() 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() 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这些老版本上我们可以模拟实现一下,顺便学习一下这三个窗口函数的实现原理
select p1.name, p1.praise_num, count(p2.praise_num) + 1 as `rank` from praise_record p1 left join praise_record p2 on p1.praise_num < p2.praise_num group by p1.name, p1.praise_num order 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()
select p1.name, p1.praise_num, count(distinct p2.praise_num) + 1 as `dense_rank` from praise_record p1 left join praise_record p2 on p1.praise_num < p2.praise_num group by p1.name, p1.praise_num order 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对点赞数做了去重,这样子对不同的点赞数返回的排名就是连续的
##使用自定义变量得先初始化 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,这样子就可以得到我们想要的序号
到此,相信大家对“怎么用MySQL窗口函数实现榜单排名”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。