MySQL GROUP BY 分組取每組前 N 條記錄：三種經典方案效能對比

測試數據

CREATE TABLE aa (
    id INT PRIMARY KEY,
    SName VARCHAR(20),
    ClsNo INT,      -- 班級
    Score INT       -- 分數
);

-- 每班 4 人，共 3 班
INSERT INTO aa VALUES
(1,'張三',1,90),(2,'李四',1,85),(3,'王五',1,80),(4,'趙六',1,75),
(5,'錢七',2,95),(6,'孫八',2,90),(7,'周九',2,85),(8,'吳十',2,80),
(9,'鄭一',3,100),(10,'王二',3,95),(11,'李三',3,90),(12,'張四',3,85);

目標：每班取前 2 名 (Top 2 per Group)

方案一：自連接 + 計數 (經典通用，MySQL 所有版本)

SELECT a.id, a.SName, a.ClsNo, a.Score
FROM aa a
LEFT JOIN aa b 
  ON a.ClsNo = b.ClsNo 
  AND a.Score < b.Score
GROUP BY a.id, a.SName, a.ClsNo, a.Score
HAVING COUNT(b.id) < 2
ORDER BY a.ClsNo, a.Score DESC;

原理解析

1. 自連接找出「分數比我高」的同班同學
2. 分數最高的 → 0 人比他高
3. 第二高 → 1 人比他高
4. 第三高 → 2 人比他高 → COUNT >= 2 被 HAVING 過濾

方案二：相關子查詢 + 計數 (可讀性較好)

SELECT * FROM aa a
WHERE 2 > (
    SELECT COUNT(*) FROM aa 
    WHERE ClsNo = a.ClsNo AND Score > a.Score
)
ORDER BY a.ClsNo, a.Score DESC;

優缺點

• ✅ 邏輯直觀：統計「比我強的人數」
• ✅ 無需 GROUP BY 所有列
• ❌ 相關子查詢逐行執行，大數據集性能差

方案三：LIMIT 在子查詢中 (MySQL 不支援，報錯)

SELECT * FROM aa 
WHERE id IN (
    SELECT id FROM aa WHERE ClsNo = a.ClsNo ORDER BY Score DESC LIMIT 2
) ORDER BY ClsNo, Score DESC;
-- ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

MySQL 8.0+ 方案四：ROW_NUMBER() 窗口函數 (最佳)

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ClsNo ORDER BY Score DESC) AS rn
    FROM aa
)
SELECT id, SName, ClsNo, Score
FROM ranked
WHERE rn <= 2
ORDER BY ClsNo, Score DESC;

性能對比 (萬級數據測試參考)

優化索引建議

-- 覆蓋索引：分組列 + 排序列 + 選擇列
CREATE INDEX idx_cls_score ON aa (ClsNo, Score DESC, id, SName);

避坑指南

1. 並列分數處理：ROW_NUMBER() 會任意打斷並列，RANK() 保留並列名次 (可能超過 N 條)，DENSE_RANK() 連續名次。按業務選擇。
2. 大分組小 Top-N：方案一/二在「分組少、組內數據極多」時性能崩塌，需應用層分頁或覆蓋索引優化
3. MySQL 5.7 變通：用戶變量模擬 ROW_NUMBER (見本系列相關文章)，但維護成本高

總結

分組 Top-N 是 SQL 經典難題。**MySQL 8.0 ROW_NUMBER() 徹底解決**。5.7 環境下，**自連接 + HAVING** 是最穩健的通用方案；**相關子查詢** 適合小數據集。建立覆蓋索引 (分組列, 排序列 DESC, 選擇列) 是所有方案的性能基石。