共計 4676 個字符，預計需要花費 12 分鐘才能閱讀完成。

本篇文章為大家展示了 MySQL 中怎么解決分類排名問題，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

01 需求介紹

考慮 MySQL 中的一個經典應用：給定一個學生考試成績表，要實現對學生按課程依成績高低進行排序。為了簡單起見，僅給定成績表，而不考慮可能關聯的學生信息表、課程信息表和教師信息表等，且成績表中僅創建 3 個關鍵字段：

 cid：課程 id，int 型，共 5 門課程

 sid：學生 id，int 型，共 8872 名學生

 score：成績，int 型，共 22366 條成績信息，分布于 10-100 之間

為了逐步分析，初始狀態不添加主鍵，也不建立任何索引。

02 子查詢

實現這一需求的最直接想法是通過子查詢，對每個分數進行統計：統計表中有多少分數比其更高，那么該分數的排名就是更高分數計數 +1。如果要區分課程排名，那么統計表時只需增加一個限制課程 id 相等的約束條件即可。

1SELECT 2 a.*, ( SELECT COUNT(score)+1 FROM scores WHERE cid = a.cid AND score   a.score ) AS  rank  3FROM 4 scores a 5ORDER BY 6 a.cid, a.score DESC;

需注意的是，在子查詢約束條件中要求 score a.score 以及 COUNT()+1，表示統計的是比該成績更高的計數 +1，例如對于 90、80、80、70 hellip; hellip; 這樣的分數得到排名結果是 1，2，2，4 hellip; hellip;；如果選用 score = a.score 和 COUNT()作為排名條件，那么得到結果是 1，3，3，4 hellip; hellip;

在未添加任何索引的情況下，這個查詢速度是相當慢的，耗時 120s。

未添加索引時的子查詢執行計劃

優化查詢的第一想法當然是添加索引：雖然外層查詢未用到任何 where 約束條件，但子查詢中用到了 cid 和 score 兩個字段判斷，于是考慮添加索引：

1CREATE INDEX idc ON scores(cid); 2CREATE INDEX ids ON scores(score);

增加索引后，查詢耗時 96s，雖然有提升，但仍難堪重任。解釋查詢計劃，發現雖然速度仍然很慢，但兩個索引確實都得到了應用：

添加獨立索引后的子查詢執行計劃

既然獨立索引無法明顯提升效率，考慮子查詢中 where 條件不是獨立字段的常值約束，而是依賴于外層循環取值的聯合約束，那么再考慮添加一個聯合索引：

CREATE INDEX idcs ON scores(cid, score);

查詢速度確實是更快了，實際用時 24s。解釋查詢計劃，發現既用到了獨立索引，也用到了聯合索引。但不得不說，24s 的響應時間對于要求 0.5s 解決戰斗的即時任務來說，仍然是不夠的。

添加聯合索引后的子查詢執行計劃

只能另辟蹊徑。

03 自連接

一般來說，對于速度較慢的子查詢任務，換做連接查詢 (join) 可以得到明顯提升。

具體到分課程排名這一具體需求，我們考慮對 scores 表進行自連接，其中連接條件為課程相等且 a 表 score 值小于 b 表 score 值，從而通過統計滿足連接條件的記錄數即可得到排名信息：

1SELECT 2 a.*, COUNT(b.score)+1 AS  rank  3FROM 4 scores a LEFT JOIN scores b ON (a.cid = b.cid AND a.score   b.score) 5GROUP BY 6 a.cid, a.sid 7ORDER BY 8 a.cid, COUNT(b.score)

需注意的是：連接方式要選用 left join，以便將 a 表中的所有分數信息都顯示出來；若是用 join，則最高分因為不存在滿足連接的記錄而被漏掉。至于連接條件中 score 值和 count()的關系類似于子查詢中的情況。

應用自連接，在不創建任何索引的情況下查詢速度與子查詢情況差不多，耗時 73s；在添加有效索引后，查詢時間 27s，效率有所提升，但與查詢方案效率相當。

未添加索引時的自連接執行計劃

添加有效索引后的自連接執行計劃

顯然，應用自連接替代子查詢的方案并沒有顯著提升查詢效率，即使是在添加了有效索引的基礎上。

進一步分析數據表發現，實際上速度慢并不能否認索引在改善查詢效率方面的能力，而僅僅是因為添加索引的字段取值較少的原因：cid 字段僅有 5 個取值 mdash; mdash; 當字段取值個數較少時，添加索引很難見效。

例如，如果換一個需求，改為按學生區分各門課程的成績排名（sid 取值數量很大），則應用索引即可有效改善查詢效率。按學生查詢成績排名 SQL 語句：

1SELECT 2 a.*, count(b.score)+1 AS  rank  3FROM 4 scores a LEFT JOIN scores b ON (a.sid = b.sid AND a.score   b.score) 5GROUP BY 6 a.sid, a.cid 7ORDER BY 8 a.sid, count(b.score)

對于如上查詢，在未添加索引時，查詢時間 34s；添加有效索引后耗時僅為 0.184s，添加索引的提升效果非常明顯。

雖然這一論斷捍衛了索引的地位作用，但如果我們的需求就是按課程進行排名呢？顯然，無論是子查詢還是自連接方案，都難以滿足我們的實時查詢需求。

只得再覓他法。

04 自定義變量

實際上，上述兩種方案之所以速度較慢，是因為都作用在兩個表上查詢，如果再考慮外層的 order by，那么執行時間復雜度粗略估計在 O(n3)量級。此時，我們考慮應用自定義變量實現更低復雜度的查詢實現。

應用自定義變量，我們不僅可以提高速度，而且還能實現 各種 排名：例如對于 90、80、80、70、60 這樣一組成績，可能有 3 種排名需求，一種是連續排名，同分時名次也繼續增加：1、2、3、4、5；第二種是同分同名，下一排名不跳級，即 1、2、2、3、4；第三種是同分同名，下一排名跳級，即 1、2、2、4、5。這三種需求應用自定義變量進行排序都可以輕松搞定（具體變量含義和思路后續給出）：

  連續排名： 

1SELECT 2 sid, cid, @curRank:=@curRank+1 AS  rank  3FROM 4 scores, (SELECT @curRank:=0) tmp 5ORDER BY 6 score DESC

  同分同名，不跳級： 

1SELECT 2 sid, cid, @curRank:=IF(score=@preScore, @curRank, @curRank+1) AS  rank , 3 @preScore:=score 4FROM 5 scores, (SELECT @curRank:=0, @preScore:=NULL) tmp 6ORDER BY 7 score

  同分同名，跳級：

1SELECT 

2 sid, cid, @curRank := IF(score=@preScore, @curRank, @totalRank) AS  rank , 3 @preScore := score, 4 @totalRank := @totalRank+1 5FROM 6 scores, (SELECT @curRank:=1, @totalRank:=1, @preScore:=NULL) tmp 7ORDER BY 8 score

以上 SQL 語句是在不進行任何分類條件下的排名：通過自定義變量（MySQL 定義變量用 @作為引導符，并用:= 表示賦值）記錄前一個排名、前一個分數值、當前的總排名，分別實現三種需求。

那么，若要實現分類排名呢，比如說區分各課程進行排名？那么只需再增加一個自定義變量，用于記錄前一個課程 cid 即可：

  若當前分類信息與前一課程 cid 相同，則繼續當前的排名處理（根據具體需求選擇三種排名中的一種）；

  若當前分類與前一課程 cid 不同，則排名信息初始化，從 1 重新開始。

以相對復雜的“同分同名、跳級”為例，此時 SQL 語句為：

1SELECT sid, cid, 2 @totalRank := IF(cid=@preCid, @totalRank+1, 1), 3 @curRank := IF(cid=@preCid, IF(score=@preScore, @curRank, @totalRank), 1) AS  rank , 4 @preScore := score, 5 @preCid := cid 6FROM 7 scores, (SELECT @curRank:=0, @totalRank:=0, @preScore:=NULL, @preCid:=NULL) tmp 8ORDER BY 9 cid, score DESC 8 score

對各變量含義解釋如下：

 @totalRank 用于記錄當前分類中的總排名，初始化為 0

 @curRank 用于記錄當前分類中的當前排名，初始化為 0

 @preScore 用于記錄上一個分數情況，初始化為 NULL

 @preCid 用于記錄上一個課程 cid，初始化為 NULL

執行流程及條件判斷為：

  若當前 cid 與前一 cid 相同，表示是同一個分類，排名在之前排名基礎增加，具體來說：

  總排名每次 +1

  若當前分數與前一分數相同，則當前排名不變；否則跳級到總排名

  若當前 cid 與前一 cid 不同，表示開始新的課程排名，總排名和當前排名均初始化為 1

基于以上 SQL 語句，執行相同的任務，耗時僅需 0.09s，其效率相當于子查詢最快速度 24s 的 266 倍，相當于自連接最快速度 27s 的 300 倍，其查詢效率可見一斑。

另外，由于上述 SQL 語句不存在 where 約束條件，所以與是否建立索引無關。

05 MySQL8.0 窗口函數

MySQL8.0 版本的一個重要更新就是增加了窗口函數，使得前面的分類排名需求變得異常簡單。

與前述類似，不同的排名需求有不同的窗口函數，而且三個函數的命名也非常形象直觀：

  連續排名：row_number()，排名即行號

  同分同名，不跳級：dense_rank()，致密排名，類似 1、2、2、3 hellip; hellip; 這種，因為不跳級，所以比較 致密

  同分同名，跳級：rank()，普通排名，類似 1、2、2、4 hellip; hellip; 這種

其中，每個窗口函數函數又必須與 over()函數配套使用，over()函數中的參數主要包括 partion by 和 order by：

 order by：與常規 SQL 語句中 order by 一致，表示按照某一字段進行排序，也區分 ASC 還是 DESC

 partion by：用作分類依據，缺省時表示不分類，對所有記錄排序；若指定某一字段，則表示在該字段間進行獨立排序，跨字段重新開始

仍以之前的分課程排名需求為例，其 SQL 語句為：

1SELECT 2 *, RANK() OVER(PARTITION BY cid ORDER BY score DESC) AS  rank  3FROM 4 scores;

查詢耗時 0.066s，比自定義變量實現的排名速度略高一點。同時，該排名方式也與索引無關。

將 RANK()替換成另外兩個窗口函數，可實現其他相應需求。

上述內容就是 MySQL 中怎么解決分類排名問題，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注丸趣 TV 行業資訊頻道。