MySQL排名函式實現

神易風發表於2020-07-22
MySql函式
本文出處MySQL排名函式實現
轉載請說明出處

現在有個需求對所有學生分數進行排名，並且列出名次。剛看到這個需求，我有點懵逼，完全沒有思路?，為什麼難一點需求，我就不會做呢? 去網上查詢資料,把所有實現都列出來，全部都要學會。

資料庫準備

建立一個分數表s_score

CREATE TABLE `s_score`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `score` int NOT NULL DEFAULT 0,
  `name` varchar(20) CHARACTER SET utf8mb4 NULL,
  PRIMARY KEY (`id`)
);

插入資料

INSERT INTO `s_score` (`name`, `score`) VALUES
('張三', 80),
('小明', 90),
('小紅', 60),
('李四', 70),
('趙武', 80),
('樑晨', 87),
('小綠', 69),
('威廉', 69),
('大衛', 91),
('王五', 96),
('趙六', 96),
('小五', 80),
('小龍', 88);

普通實現

在MySQL8.0推出Rank排名函式RANK，完全支援這種需求，但是必須MySQL8.0 以上版本才支援這個特性。8.0以下的版本有什麼方法實現呢，使用使用者變數，記錄名次。
使用者變數：以"@"開始，形式為"@var_name",以區分使用者變數及列名。它可以是任何隨機的，複合的標量表示式，只要其中沒有列指定。下面寫一個小例子，展示如何使用使用者變數

select @a:=1 a,@b:=@a+1 b

執行結果

a b
1 2

:= 是賦值的意思，與程式語言賦值有點區別。下面開始展示使用簡單SQL實現RANK排名函式效果

使用者變數簡單實現名次顯示

SELECT name,score, @rank:=@rank+1 `rank` from s_score s,(select @rank:=0) q ORDER BY score desc
name score rank
趙六 96 1
王五 96 2
大衛 91 3
小明 90 4
小龍 88 5
樑晨 87 6
小五 80 7
張三 80 8
趙武 80 9
李四 70 10
威廉 69 11
小綠 69 12
小紅 60 13

並排名次展示

現在還有一個問題，出現分數相同，並列排名，名次應該相同。我們使用一個temp變數來記錄前一個分數值，判斷前面分數是否與當前相等，相等直接返回上一個排名情況，否則排名+1。

select name,score,case when @temp_score=score then @rank when @temp_score:=score then @rank:=@rank+1 END 
   `rank` from s_score s,(select @rank:=0,@temp_score:=NULL) q ORDER BY score desc
name score rank
趙六 96 1
王五 96 1
大衛 91 2
小明 90 3
小龍 88 4
樑晨 87 5
小五 80 6
張三 80 6
趙武 80 6
李四 70 7
威廉 69 8
小綠 69 8
小紅 60 9

並排名次跳過

如果出現並列排名，下一個名次將自動跳過，比如出現兩個並列第一，91應該變成第三名了，名次和人數相對應。

SELECT name,score,rank from (
SELECT name ,score,@rank :=IF( @temp_score = score, @rank, @rank_incr ) `rank`,@rank_incr := @rank_incr + 1,
    @temp_score := score FROM score s,(SELECT@rank := 0,@temp_rank := NULL,@rank_incr := 1 ) q ORDER BY score DESC) a
name score rank
趙六 96 1
王五 96 1
大衛 91 3
小明 90 4
小龍 88 5
樑晨 87 6
小五 80 7
張三 80 7
趙武 80 7
李四 70 10
威廉 69 11
小綠 69 11
小紅 60 13

使用SQL視窗函式

視窗函式的基本語法如下：

select 排序函式/聚合函式 over (<partition by ...> 分割槽欄位 order by 排序欄位)

注意over 後面有一個空格的，這個語法有點蛋疼，我自己試了十幾次才書寫成功。
根據維基百科解釋：視窗函式允許在當前記錄之前和之後訪問記錄中的資料。視窗函式定義一幀或一列視窗，其中當前行周圍具有給定的長度，並跨視窗中的資料集執行計算。可以這樣理解，視窗就是資料集合，函式就是計算資料方法。

partiton by是可選的。如果不使用partition by，那麼就是將整張表作為一個集合，最後使用排序函式得到的就是每一條記錄根據排序列的排序編號。
排序函式主要有rank()、dense_rank、row_number，他們主要區別：

  • rank(): 對同一個欄位排序，出現相同時，會並列排名，並且會出現排名間隙。
  • dense_rank() : 對同一個欄位排序，出現相同時，會出現並列排名，排名連續的
  • row_number(): 對同一個欄位排序，排名是聯絡的，即使出現相同，不會並列排名次
    select name,score, RANK() over (ORDER BY score DESC) `rank`,ROW_NUMBER() over (order by score DESC) `row`,
    DENSE_RANK()over (ORDER BY score DESC) `dense` from s_score
name score rank row dense
趙六 96 1 1 1
王五 96 1 2 1
大衛 91 3 3 2
小明 90 4 4 3
小龍 88 5 5 4
樑晨 87 6 6 5
趙武 80 7 7 6
小五 80 7 8 6
張三 80 7 9 6
李四 70 10 10 7
小綠 69 11 11 8
威廉 69 11 12 8
小紅 60 13 13 9

以上就是排序名次全部實現方式了，還有其他實現方式，麻煩在評論裡補充一下。

參考文件

https://cloud.tencent.com/developer/article/1562954
https://www.jianshu.com/p/bb1b72a1623e

相關文章