信任大家在日常的开发中经常会碰到榜单类的活动需求，通常在榜单中都会要求回来排名，今日咱们就用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 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()

dense_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对点赞数做了去重，这姿态对不同的点赞数回来的排名就是接连的

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，这姿态就能够得到咱们想要的序号

总结

1.rank()函数回来的排名会产生跳跃

2.dense_rank()函数回来的排名是接连的

3.row_number()函数回来的排名类似序号

4.窗口函数是MySQL8.0新增的特性，如果在低版本的MySQL要自己模仿完成一下