MySQL 查詢各班級前三名學生：高效 SQL 寫法與效能優化

面試真題：如何用 SQL 查詢各班級成績前三名？

昨天去一家公司面試，被這道題難住了，錯失了一次好機會。回來後我深入研究了這個問題，發現其中有不少陷阱，特別是在 MySQL 環境下處理「前 N 名」且包含並列名次時，寫法需要格外謹慎。

表結構與樣本數據

假設有一張 student 表，包含學生 ID、班級 ID、姓名、分數等欄位。目標是查詢每個班級成績排名前三的學生（若有並列同分，可能超過三筆）。

常見錯誤嘗試

1. 使用 IN + LIMIT 子查詢（語法錯誤）

SELECT * FROM student a 
WHERE a.id IN (
    SELECT b.id FROM student b 
    WHERE b.classId = a.classId 
    ORDER BY grade DESC LIMIT 0,3
);

問題： MySQL 不允許在 IN 子查詢中使用 LIMIT，會直接報語法錯誤。此外，若前三名有並列同分，單純 LIMIT 3 會漏掉同分學生。

2. 使用 NOT EXISTS + 計數邏輯（效能極差）

SELECT * FROM student b
WHERE NOT EXISTS (
    SELECT * FROM student c 
    WHERE c.classId = b.classId 
      AND b.grade < c.grade 
    GROUP BY c.classId 
    HAVING COUNT(*) > 3
);

邏輯： 若班級內有超過 3 位學生分數高於該生，則該生不在前三名。

致命缺陷： 這是典型的關聯子查詢，會觸發 Nested Loop。若表有 10 萬行，需執行 1 + 100,000 次查詢，效能不可接受。用 Java 伪碼模擬：

List<Student> students = studentService.queryAll(); // 1 次查詢
for (Student s : students) {
    // 每位學生再查一次：是否為班級前三名？ // N 次查詢
}

高效解法：窗口函數（MySQL 8.0+）

MySQL 8.0 引入窗口函數，可優雅解決此問題：

WITH ranked AS (
    SELECT 
        *,
        DENSE_RANK() OVER (PARTITION BY classId ORDER BY grade DESC) AS rnk
    FROM student
)
SELECT id, classId, name, grade
FROM ranked
WHERE rnk <= 3;

• DENSE_RANK() 會給同分學生相同名次，且不跳過名次（如 1,2,2,3），完美符合「前三名含並列」需求。
• 單次掃表，效能極佳。

舊版本 MySQL（5.7 及以下）替代方案

方案一：用戶變量模擬 ROW_NUMBER

SELECT id, classId, name, grade
FROM (
    SELECT 
        *,
        @rn := IF(@prev_class = classId, @rn + 1, 1) AS rn,
        @prev_class := classId
    FROM student
    CROSS JOIN (SELECT @rn := 0, @prev_class := NULL) vars
    ORDER BY classId, grade DESC
) t
WHERE rn <= 3;

注意：此法依賴變量賦值順序，在複雜查詢或並行執行時可能不穩定，建議僅用於簡單場景。

方案二：自連接計數法（通用但較慢）

方案適用版本並列處理效能推薦度
窗口函數 DENSE_RANKMySQL 8.0+完美極高⭐⭐⭐⭐⭐
用戶變量模擬5.7 及以下可行但風險中等⭐⭐⭐
自連接計數所有版本完美低⭐⭐
NOT EXISTS 關聯子查詢所有版本完美極低❌ 不推薦


總結

面試若遇此題，首推 MySQL 8.0 窗口函數，展現現代 SQL 功力。
舊版本環境下，可說明用戶變量法的原理與局限，並強調生產環境應升級或用應用層分頁彌補。
切忌使用 NOT EXISTS + HAVING COUNT 的雙層嵌套，那是效能殺手。

掌握這些寫法，下次面試再遇「各分組 Top N」就不會慌了！