MySQL 分組取 Top N 筆資料與自動編號序列單一 SQL 寫法

需求場景

針對同一 cid 分組，依 number 遞減排序，只取前 N 筆（如每類熱門前 3 名作者）。

單一 SQL 解法（相關子查詢 + 計數比較）

SELECT a.cid, a.author, a.number
FROM (
    SELECT cid, author, COUNT(*) AS number
    FROM test
    GROUP BY cid, author
) a
WHERE 3 > (
    SELECT COUNT(*)
    FROM (
        SELECT cid, author, COUNT(*) AS number
        FROM test
        GROUP BY cid, author
    ) b
    WHERE a.cid = b.cid
      AND a.number < b.number
)
ORDER BY a.cid, a.number DESC;

原理解析

• 外層 a 為各分組彙總後的結果集。
• 子查詢 b 計算「同 cid 下，number 比目前列大的有幾筆」。
• WHERE 3 > ... 表示「比我大的不到 3 筆」→ 即排名前 3。
• 將 3 改為參數 @N 即可動態調整 Top N。

效能提示

• 適合中小資料量（< 10 萬筆）。
• 大資料量建議改用 視窗函數 (MySQL 8.0+)：ROW_NUMBER() OVER (PARTITION BY cid ORDER BY number DESC) AS rn 再 WHERE rn <= N，執行計畫更優。
• 確保 (cid, author) 有複合索引，加速 GROUP BY 与子查詢關聯。

補充：產生自動遞增序號

SET @ROW = 0;
SELECT @ROW := @ROW + 1 AS seq, t.*
FROM (SELECT * FROM test ORDER BY cid, number DESC) t;

搭配上方分組查詢，可直接產生含分組內排名的報表。