MySQL 排行榜查詢 SQL 寫法大全：ROW_NUMBER、變量模擬、連接法多種實現對比

核心需求

對業務數據按指標 (分數、銷量、積分) 排序，產生名次，支援分組 (如按班級、部門、區域) 內排名、Top N 篩選、分頁展示。

方案一：MySQL 8.0+ 窗口函數 (標準、最高效、可讀性最強)

-- 全局排名
SELECT id, name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
       RANK() OVER (ORDER BY score DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS drk
FROM scores;

-- 分組排名 (按班級)
SELECT id, class_id, name, score,
       ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS class_rank
FROM scores;

-- 取各組 Top 3
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rn
    FROM scores
)
SELECT * FROM ranked WHERE rn <= 3;

差異： ROW_NUMBER 連續不重複、RANK 跳過並列名次 (1,2,2,4)、DENSE_RANK 不跳過 (1,2,2,3)。

方案二：MySQL 5.7/5.6 會話變量模擬 (生產廣泛使用、需注意順序)

-- 全局排名
SELECT id, name, score,
       @rn := @rn + 1 AS rank
FROM scores, (SELECT @rn := 0) AS init
ORDER BY score DESC;

-- 分組排名 (關鍵：變量重置邏輯)
SELECT id, class_id, name, score,
       @rn := IF(@prev_class = class_id, @rn + 1, 1) AS class_rank,
       @prev_class := class_id
FROM scores, (SELECT @rn := 0, @prev_class := -1) AS init
ORDER BY class_id, score DESC;

核心邏輯： ORDER BY 保證同組數據連續、IF 判斷分組變化重置計數器、@prev_class := class_id 在 SELECT 列表中更新狀態 (順序敏感！)。

方案三：自連接計數法 (純 SQL、無變量、效能一般)

-- 全局排名：計算有多少分數大於等於當前行
SELECT s1.id, s1.name, s1.score,
       COUNT(DISTINCT s2.score) + 1 AS rank  -- +1 因為不含自己
FROM scores s1
LEFT JOIN scores s2 ON s2.score > s1.score
GROUP BY s1.id, s1.name, s1.score
ORDER BY s1.score DESC;

-- 分組排名
SELECT s1.id, s1.class_id, s1.name, s1.score,
       COUNT(DISTINCT s2.score) + 1 AS class_rank
FROM scores s1
LEFT JOIN scores s2 ON s2.class_id = s1.class_id AND s2.score > s1.score
GROUP BY s1.id, s1.class_id, s1.name, s1.score
ORDER BY s1.class_id, s1.score DESC;

缺點： 自連接產生笛卡爾積中間結果，大數據集極慢；COUNT(DISTINCT) 開銷大。

方案四：相關子查詢 (邏輯直觀、大數據慎用)

SELECT id, name, score,
       (SELECT COUNT(*) + 1 FROM scores s2 WHERE s2.score > s1.score) AS rank
FROM scores s1
ORDER BY score DESC;

進階場景：並列名次處理與分頁

並列名次 (同分同名次、下一名次跳過)

-- 8.0 窗口函數
RANK() OVER (ORDER BY score DESC)

-- 變量法
SELECT id, name, score,
       @rank := IF(@prev_score = score, @rank, @rn) AS rank,
       @rn := @rn + 1,
       @prev_score := score
FROM scores, (SELECT @rank := 0, @rn := 0, @prev_score := -1) init
ORDER BY score DESC;

排名分頁 (取第 11~20 名)

-- 8.0 CTE + 窗口函數
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
    FROM scores
)
SELECT * FROM ranked WHERE rn BETWEEN 11 AND 20;

-- 變量法 (需子查詢先排序賦值)
SELECT * FROM (
    SELECT *, @rn := @rn + 1 AS rn
    FROM scores, (SELECT @rn := 0) init
    ORDER BY score DESC
) t WHERE rn BETWEEN 11 AND 20;

效能對比與選型建議

實戰優化技巧

1. 覆蓋索引： INDEX idx_score (score DESC, id) 或 INDEX idx_class_score (class_id, score DESC, id) 支援 ORDER BY ... LIMIT 推遲物化
2. 避免 SELECT *： 只取需要列，減少傳輸與排序開銷
3. 大數據分頁： ID 游標法 WHERE id > last_id ORDER BY score DESC LIMIT 20 (需業務允許)
4. 緩存排名結果： 排名變動頻率低時，Redis 緩存 Top 100/分頁片段
5. 異步計算： 定時任務計算排名寫入 rank_snapshot 表，查詢直接讀快照

結語

MySQL 8.0+ 請無腦用窗口函數 (ROW_NUMBER/RANK/DENSE_RANK)，標準、高效、可維護。5.7/5.6 環境會話變量法是主流選擇，但務必嚴格控制 ORDER BY 順序與變量賦值順序。自連接/子查詢僅供教學或極小數據集。生產系統建議結合「覆蓋索引 + 異步快照 + Redis 緩存」三層防禦，將排名查詢延遲壓至毫秒級。