MySQL 獲取每個分類前 N 條記錄：高效 SQL 寫法與原理解析

需求：從文章表 Article(id, category, insert_date) 中，查詢每個分類下最新的前 N 篇文章。本文提供經典的「自連接 + 計數」單條 SQL 寫法，並解析其執行原理、適用場景與優化方向。

一、核心 SQL 寫法

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

@N 為所需前 N 條（如 7）。

二、原理解析

1. 自連接構建排名：A LEFT JOIN B ON A.cat=B.cat AND A.dt <= B.dt，對於 A 表的每一行，B 表匹配「同分類且日期不早於它」的所有行。
2. 計數即排名：COUNT(B.insert_date) 等於「比當前行晚或同時間的行數」，即倒序排名。
3. HAVING 過濾：COUNT(...) <= N 保留排名前 N 的行（含並列同時間行）。
4. 回主表取完整列：內層只輸出 category, insert_date，外層 JOIN 回主表取 *，避免內層攜帶大字段導致臨時表膨脹。

三、實戰變體：產品分類表

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;

四、性能特徵與優化建議

五、現代替代方案（MySQL 8.0+）

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY insert_date DESC) AS rn
  FROM Article
)
SELECT * FROM ranked WHERE rn <= @N;

• 窗口函數：線性掃描，無自連接平方級開銷，性能提升數量級。
• 索引建議：(category, insert_date DESC) 支援 Index Skip Scan 加速分區排序。

六、總結

「自連接計數」是 MySQL 5.7 及之前獲取分組 Top-N 的經典寫法，邏輯清晰、兼容性好，但不適合大分類大數據量。MySQL 8.0 應優先使用 ROW_NUMBER() 窗口函數；舊版本若數據量大，建議應用層分批查詢或引入 Redis 預計算緩存。