MySQL 獲取每組前 N 條記錄：分組 Top-N 查詢實戰

MySQL 獲取每組前 N 條記錄：分組 Top-N 查詢實戰

需求：文章表 Article(Id, Category, InsertDate)，查詢每個分類下最新的 N 篇文章。本文提供經典自連接計數法與 MySQL 8.0 窗口函數法兩種方案。

方案一：自連接計數法（兼容 MySQL 5.6+）

SELECT A1.*
FROM Article AS A1
INNER JOIN (
    SELECT A.Category, A.InsertDate
    FROM Article AS A
    LEFT JOIN Article AS B
        ON A.Category = B.Category
       AND A.InsertDate <= B.InsertDate
    GROUP BY A.Category, A.InsertDate
    HAVING COUNT(B.InsertDate) <= @N
) AS B1
    ON A1.Category = B1.Category
   AND A1.InsertDate = B1.InsertDate
ORDER BY A1.Category, A1.InsertDate DESC;

原理：對同分類中日期 >= 當前行的行數計數，若 ≤ N 則為 Top-N。@N 為參數。

方案二：窗口函數 ROW_NUMBER()（MySQL 8.0+ 推薦）

WITH ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Category ORDER BY InsertDate DESC) AS rn
    FROM Article
)
SELECT Id, Category, InsertDate
FROM ranked
WHERE rn <= 3;  -- 取前 3

優點：語法清晰、效能優於自連接（避免 O(N²) 計數）、易擴展（如 RANK()、DENSE_RANK() 處理並列）。

實戰變體：產品分類表取每大類前 7 個小類

SELECT A1.*
FROM prcKx AS A1
INNER JOIN (
    SELECT A.superId, A.prcid
    FROM prcKx AS A
    LEFT JOIN prcKx AS B
        ON A.superId = B.superId
       AND A.prcid <= B.prcid
    GROUP BY A.superId, A.prcid
    HAVING COUNT(B.prcid) <= 7
) AS B1
    ON A1.superId = B1.superId
   AND A1.prcid = B1.prcid
ORDER BY A1.superId, A1.prcid;

效能建議

• 自連接法需在 (Category, InsertDate) 建立復合索引。
• 窗口函數法同樣受益於 (Category, InsertDate DESC) 索引。
• 大數據量（百萬級）建議用窗口函數或應用層分頁彙總。