MySQL 分組查詢取前 N 筆資料完整指南

在 MySQL 中，GROUP BY 分組後如何取得每組的前 N 條記錄是常見需求。本文將詳細介紹多種實現方案，並附帶完整測試數據與效能分析。

測試數據準備

建立測試表 t_subject 並插入樣本資料：

mysql> select * from t_subject;
| id | uid | subject |
| 1  | 1   | aa      |
| 2  | 2   | bb      |
| 3  | 3   | cc      |
| 4  | 4   | dd      |
| 5  | 2   | ee      |
| 6  | 2   | rr      |
| 7  | 3   | tt      |
| 8  | 2   | yy      |
| 9  | 3   | qq      |
| 10 | 4   | oo      |
| 11 | 3   | pp      |
| 12 | 3   | kk      |
| 13 | 1   | mm      |
| 14 | 4   | nn      |
| 15 | 1   | ss      |
| 16 | 4   | vv      |
| 17 | 1   | ff      |

目標結果

按 uid 分組，取每組前 3 條記錄（按 subject 排序）：

| uid | group_concat(subject) |
| 1   | aa,ff,mm              |
| 2   | yy,rr,ee              |
| 3   | kk,pp,qq              |
| 4   | nn,dd,vv              |

方法一：相關子查詢 + 變量排名（MySQL 8.0 以前）

SELECT uid, GROUP_CONCAT(subject ORDER BY subject) 
FROM (
    SELECT id, uid, subject,
           (SELECT COUNT(*) 
            FROM t_subject 
            WHERE uid = t.uid 
            AND subject <= t.subject) AS rk
    FROM t_subject t
) t1
WHERE rk <= 3
GROUP BY uid;

原理：利用相關子查詢計算每條記錄在組內的排名，再過濾排名前 3 的記錄。

方法二：LEFT JOIN 自連接 + HAVING 過濾

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. 分數最低的學生會產生 3 條匹配記錄，第二低產生 2 條，前兩名產生 0-1 條
3. HAVING COUNT(b.id) < 2 保留前兩名

方法三：相關子查詢 + COUNT 過濾

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;

優點：邏輯直觀，直接判斷「比我成績高的人是否少於 2 個」。

方法四：LIMIT 子查詢（MySQL 8.0+ 不支援）

-- 此寫法在舊版本會報錯：ERROR 1235 (42000)
SELECT * FROM aa 
WHERE id IN (SELECT id FROM aa WHERE ClsNo = a.ClsNo ORDER BY Score DESC LIMIT 2)
ORDER BY a.ClsNo, a.Score DESC;

注意：MySQL 5.x 不支援在 IN/ALL/ANY/SOME 子查詢中使用 LIMIT。

現代最佳實踐：窗口函數（MySQL 8.0+）

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY subject) AS rn
    FROM t_subject
)
SELECT uid, GROUP_CONCAT(subject ORDER BY subject)
FROM ranked
WHERE rn <= 3
GROUP BY uid;

推薦理由：語法清晰、效能最佳、符合 SQL 標準。

效能對比與建議

方法	適用版本	效能	可讀性
窗口函數	8.0+	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐
相關子查詢排名	5.x/8.0	⭐⭐	⭐⭐⭐
LEFT JOIN + HAVING	5.x/8.0	⭐⭐⭐	⭐⭐
相關子查詢 COUNT	5.x/8.0	⭐⭐⭐	⭐⭐⭐⭐

總結：MySQL 8.0 以上首選窗口函數；舊版本推薦相關子查詢 COUNT 寫法，平衡效能與可讀性。