SQL排名問題，100% leetcode答案大公開！

（首先原諒我最近新番看多了，起了一個中二的名字）

最近在找實習，所以打算系統總結（復習）一下sql中經常遇到問題，不管是刷leetcode還是牛客的sql題，有一個問題總是繞不開的，那就是排名問題，其實對于MySql8.0以上版本來說，排名問題已經很容易解決了，因為MySql8.0之后開始支持三個視窗函式，分別是rank(),dense_rank()以及row_number()，這三個視窗函式對應了排名問題中最常見的三種情況，而對于之前的版本，則需要模擬這幾個函式，

網上也有很多相關的文章，但實際上他們給出的代碼都無法100%通過leetcode的樣例，于是就想在這里重新總結一下sql中的排名問題，我們以分數排名為例，假設有一個資料表scores包括兩個欄位id和score，需要對score進行排名，

不同的排名需求適合不同的場景，但為了方便比較，就不分開舉例了，對于排名的結果，一般會包括以下幾種情況：

1. 同樣的分數不同的名次，且排名連續

如果是這樣的排名需求，排名的結果應該是：

• 視窗函式 row_number()

  SELECT id, score, row_number() over (order by score desc) as 'rank'
  FROM Scores;
  

• 模擬視窗函式

  SET @curRank = 0;
  SELECT id, Score, (@currank := @currank + 1) As 'rank' 
  From Scores
  ORDER BY score DESC;
  

  自行模擬視窗函式的關鍵就在于要設定一個變數來保存當前的排名，

2. 同樣的分數相同的名次，且排名連續（Leetcode 178）

如果是這樣的排名需求，排名的結果應該是：

• 使用視窗函式 dense_rank()

  SELECT id, score, dense_rank() over (order by score desc) as 'rank'
  FROM Scores;
  

• 使用變數模擬視窗函式

  對于這種排名，網上給出的代碼大多是這樣的：

  SELECT tmp.score, 
         @ranking := case
            when @lastscore = tmp.score then @ranking
            when @lastscore := tmp.score then @ranking +1
         end as 'rank'
  FROM (select * from scores order by score desc) tmp,
       (select @ranking := 0, @lastscore := null) r;
  

  這段代碼可能在一般的資料表中都沒問題，但在leetcode上是不能完全ac的，主要有兩個問題：

  • 回傳的rank排名是字串，而不是數字，會導致樣例失敗
  • 如果表中score有值為0，排名結果就會是null，

  這種做法的思想是：

  • 如果當前的score跟上一行score（@lastscore）相等，則@ranking不增加（@ranking := @ranking）;
  • 否則就 @lastscore := tmp.score （這一句在score不為0的時候永遠為真），給 @lastscore 賦新值的同時，@ranking增加1（@ranking := @ranking + 1），

  但是當score為0時，兩個when中的運算式都是False，所以什么都不執行，導致0值的結果為null，

  針對這種情況，可以稍微改進一下：

  SELECT tmp.score, 
         @ranking := case
            when @lastscore = tmp.score then @ranking +0
            when @lastscore := tmp.score then @ranking +1
            else @ranking := @ranking + 1
         end as 'rank'
  FROM (select * from scores order by score desc) tmp,
  	 (select @ranking := 0, @lastscore := null) r;
  

  • 第一點是在第一個when處增加了 +0 ，進行型別轉換
  • 第二點是增加了 else @ranking := @ranking + 1 ，保證出現0值的時候，仍然可以進行排名，

  這樣的寫法是可以通過leetcode的所有樣例的，

  

• 使用聯結模擬視窗函式

  SET @currank := 0;
  Select os.id, r.Score, r.rank
  From Scores os 
  LEFT JOIN (SELECT *, (@currank := @currank + 1) As 'rank'
             From (Select *
                   	From Scores
                   	Group BY Score
                   	ORDER BY score DESC
                   ) AS ra
             ) AS r
  ON os.score = r.score
  ORDER BY r.score DESC;
  

  這種查詢其實可以看做兩步，第一步是使用Group BY分組，進行了一個無重復值的排名（其實就是第一種情況），之后再把這個排名表Left Join到原始的表中，然后對組合表在進行一次排名，

3. 同樣的分數相同的名次，且排名不連續

這種情況應該是最符合現實中分數排名的，排名結果如下：

• 使用視窗函式rank（）

  SELECT id, score, rank() over (order by score desc) as 'rank'
  FROM Scores;
  

• 使用變數模擬視窗函式

  SELECT tmp2.id, tmp2.score, tmp2.ranking AS 'rank'
  FROM (SELECT tmp.*,
               @rownum := @rownum+1 AS rownum,
               @ranking := case
                  when @lastscore = tmp.score then @ranking + 0
                  when @lastscore := tmp.score then @rownum + 0
                  else @rownum + 0
               end as ranking
        FROM (select * from scores order by score desc) tmp,
             (select @rownum := 0, @lastscore := null, @ranking := 0) r
        ) AS tmp2
  

  這里的做法其實相當于第一種情況和第二種情況的結合，用兩個變數分別存盤排名和當前的行數，

  • 如果當前的score跟上一行score（@lastscore）相等，則@ranking不增加（@ranking := @ranking +0 ）;
  • 否則就 @lastscore := tmp.score ，這里跟第二種情況不同在于@ranking不再是增加1，而是賦值為行數（@rownum），

以上就是遇到比較多的排名問題的解法啦，關于視窗函式，其實還有更多的用途，比如分組排名，后面可能專門寫一篇來介紹這幾個排名視窗函式，

最后慣例：

能力有限，如有錯漏，多多包涵，歡迎指正！

標籤：其他

上一篇：使用Redis+SpringBoot實作定時任務測驗

下一篇：【資料庫】Redis(2)--Redis的常用資料型別及命令