前言:
在某些應用場景中,我們經常會遇到一些排名的問題,比如按成績或年齡排名。排名也有多種排名方式,如直接排名、分組排名,排名有間隔或排名無間隔等等,這篇文章將總結幾種MySQL中常見的排名問題。
創建測試表
<code>create table scores_tb ( id int auto_increment primary key, xuehao intnot
null, score intnot
null ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into scores_tb (xuehao,score) values (1001
,89
),(1002
,99
),(1003
,96
),(1004
,96
),(1005
,92
),(1006
,90
),(1007
,90
),(1008
,94
); mysql> select * from scores_tb; +----+--------+-------+| id |
xuehao| score |
+----+--------+-------+| 1 |
1001
| 89 |
| 2 |
1002
| 99 |
| 3 |
1003
| 96 |
| 4 |
1004
| 96 |
| 5 |
1005
| 92 |
| 6 |
1006
| 90 |
| 7 |
1007
| 90 |
| 8 |
1008
| 94 |
+----+--------+-------+/<code>
1.普通排名
按分數高低直接排名,從1開始,往下排,類似於row number。下面我們給出查詢語句及排名結果。
<code> SELECT xuehao, score, @curRank:
= @curRank +1
AS rank FROM scores_tb, ( SELECT @curRank:
=0
) r ORDER BY score desc; +--------+-------+------+| xuehao |
score| rank |
+--------+-------+------+| 1002 |
99
| 1 |
| 1003 |
96
| 2 |
| 1004 |
96
| 3 |
| 1008 |
94
| 4 |
| 1005 |
92
| 5 |
| 1006 |
90
| 6 |
| 1007 |
90
| 7 |
| 1001 |
89
| 8 |
+--------+-------+------+/<code>
上述查詢語句中,我們申明瞭一個變量 @curRank ,並將此變量初始化為0,查得一行將此變量加一,並以此作為排名。我們看到這類排名是沒間隔的並且有些分數相同但排名不同。
2.分數相同,名次相同,排名無間隔
<code># 查詢語句SELECT
xuehao
,score
,CASE
WHEN
@prevRank
= score THEN@curRank
WHEN
@prevRank
:= score THEN@curRank
:=@curRank
+1
END AS rank FROM scores_tb, (SELECT@curRank
:=0
,@prevRank
:= NULL) r ORDER BY score desc; # 排名結果 +--------
+-------
+------
+ |xuehao
|score
|rank
| +--------
+-------
+------
+ |1002
|99
|1
| |1003
|96
|2
| |1004
|96
|2
| |1008
|94
|3
| |1005
|92
|4
| |1006
|90
|5
| |1007
|90
|5
| |1001
|89
|6
| +--------
+-------
+------
+/<code>
3.並列排名,排名有間隔
另外一種排名方式是相同的值排名相同,相同值的下一個名次應該是跳躍整數值,即排名有間隔。
<code># 查詢語句SELECT
xuehao
,score
,rank
FROM
(SELECT xuehao, score,@curRank
:= IF(@prevRank
= score,@curRank
,@incRank
) AS rank,@incRank
:=@incRank
+1
,@prevRank
:= score FROM scores_tb, ( SELECT@curRank
:=0
,@prevRank
:= NULL,@incRank
:=1
) r ORDER BY score desc)s
; # 排名結果 +--------
+-------
+------
+ |xuehao
|score
|rank
| +--------
+-------
+------
+ |1002
|99
|1
| |1003
|96
|2
| |1004
|96
|2
| |1008
|94
|4
| |1005
|92
|5
| |1006
|90
|6
| |1007
|90
|6
| |1001
|89
|8
| +--------
+-------
+------
+/<code>
上面介紹了三種排名方式,實現起來還是比較複雜的。好在MySQL8.0增加了窗口函數,使用內置函數可以輕鬆實現上述排名。
MySQL8.0 利用窗口函數實現排名
MySQL8.0中可以利用 ROW_NUMBER(),DENSE_RANK(),RANK() 三個窗口函數實現上述三種排名,需要注意的一點是as後的別名,千萬不要與前面的函數名重名,否則會報錯,下面給出這三種函數實現排名的案例:
<code>select
xuehao,score, ROW_NUMBER()OVER
(order
by
scoredesc
)as
row_rfrom
scores_tb;select
xuehao,score,DENSE_RANK
()OVER
(order
by
scoredesc
)as
dense_rfrom
scores_tb;select
xuehao,score,RANK
()over
(order
by
scoredesc
)as
rfrom
scores_tb;SELECT
xuehao,score, ROW_NUMBER()OVER
wAS
'row_r'
,DENSE_RANK
()OVER
wAS
'dense_r'
,RANK
()OVER
wAS
'r'
FROM
`scores_tb`
WINDOW
wAS
(ORDER
BY
`score`
desc
); + | xuehao | score | row_r | dense_r | r | + | 1002 | 99 | 1 | 1 | 1 | | 1003 | 96 | 2 | 2 | 2 | | 1004 | 96 | 3 | 2 | 2 | | 1008 | 94 | 4 | 3 | 4 | | 1005 | 92 | 5 | 4 | 5 | | 1006 | 90 | 6 | 5 | 6 | | 1007 | 90 | 7 | 5 | 6 | | 1001 | 89 | 8 | 6 | 8 | +/<code>
總結:
本文給出三種不同場景下實現統計排名的SQL,可以根據不同業務需求選取合適的排名方案。對比MySQL8.0,發現利用窗口函數可以更輕鬆實現排名,其實業務需求遠遠比我們舉的示例要複雜許多,用SQL實現此類業務需求還是需要慢慢積累的。
參考:
- https://www.cnblogs.com/caicaizi/p/9803013.html
- https://blog.csdn.net/sqsltr/article/details/94408487