MySQL窗口函數(shù)實(shí)現(xiàn)榜單排名

更新時(shí)間：2023年04月11日 15:19:36 作者：octobershen

相信大家在日常的開發(fā)中經(jīng)常會(huì)碰到榜單類的活動(dòng)需求，本文主要介紹了MySQL窗口函數(shù)實(shí)現(xiàn)榜單排名，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

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);

然后就可以開始實(shí)現(xiàn)我們的需求：返回點(diǎn)贊的榜單，并返回排名

rank()

使用rank()函數(shù)返回點(diǎn)贊的榜單， rank() over()

## 注意這里返回的rank字段要用反引號(hào)包起來
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()函數(shù)的時(shí)候相同的點(diǎn)贊數(shù)會(huì)返回相同的排名，排名會(huì)產(chǎn)生跳躍，最終的排名不是連續(xù)的

dense_rank()

使用dense_rank()函數(shù)返回點(diǎn)贊的榜單， 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()函數(shù)相同的是，相同點(diǎn)贊數(shù)會(huì)返回相同的排名，但是dense_rank()返回的最終排名是連續(xù)的排名

row_number()

row_number()函數(shù)返回點(diǎn)贊的榜單，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()函數(shù)適合當(dāng)返回的列表只需要序號(hào)時(shí)使用

以上三個(gè)函數(shù)都是MySQL8.0新加入的，所以在MySQL5.7這些老版本上我們可以模擬實(shí)現(xiàn)一下，順便學(xué)習(xí)一下這三個(gè)窗口函數(shù)的實(shí)現(xiàn)原理

rank()函數(shù)的模擬實(shí)現(xiàn)

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 |
+-------+------------+------+

我們可以使用自聯(lián)接的方式將每個(gè)分?jǐn)?shù)低于當(dāng)前行分?jǐn)?shù)的記錄計(jì)數(shù)，最后將計(jì)數(shù)值加1作為當(dāng)前行的排名，來模擬實(shí)現(xiàn)rank()

dense_rank()的模擬實(shí)現(xiàn)

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的實(shí)現(xiàn)與rank差不多，唯一的區(qū)別是增加了distinct對(duì)點(diǎn)贊數(shù)做了去重，這樣子對(duì)不同的點(diǎn)贊數(shù)返回的排名就是連續(xù)的

row_number的模擬實(shí)現(xiàn)

##使用自定義變量得先初始化
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 |
+-------+------------+------------+

我們可以使用一個(gè)rowNum變量來記錄行號(hào)，每一行的數(shù)據(jù)rowNUm都+1，這樣子就可以得到我們想要的序號(hào)