「每個分類取最新 N 條記錄」是經典的 Top-N-per-group 問題。本文提供三種主流解法,從相關子查詢到視窗函數,對比效能與適用場景,助你選擇最優方案。
表 Article(id, Category, InsertDate),需求:每個 Category 取 InsertDate 最新的 N 條記錄。
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;
原理:對每個 (Category, InsertDate) 組合,計算「比它晚或同時間」的記錄數。若 ≤ N,說明它在 Top N 內。
優點:全版本支援,無視窗函數依賴。
缺點:大資料量時自我連接產生笛卡爾積,效能指數級下降。
SET @rownum := 0, @prev_cat := '';
SELECT id, Category, InsertDate
FROM (
SELECT
id, Category, InsertDate,
@rownum := IF(@prev_cat = Category, @rownum + 1, 1) AS rn,
@prev_cat := Category
FROM Article
ORDER BY Category, InsertDate DESC
) AS t
WHERE rn <= @N;
優點:單掃描,O(N) 複雜度,效能極佳。
缺點:依賴變量賦值順序,MySQL 8.0+ 優化器可能破壞語義;ORDER BY 必須在子查詢中保證。
WITH ranked AS (
SELECT
id, Category, InsertDate,
ROW_NUMBER() OVER (PARTITION BY Category ORDER BY InsertDate DESC) AS rn
FROM Article
)
SELECT id, Category, InsertDate
FROM ranked
WHERE rn <= @N
ORDER BY Category, InsertDate DESC;
優點:標準 SQL、語義清晰、優化器支援最好、可並行執行。
缺點:需 MySQL 8.0+。
-- 取每個大類前 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;
| 方案 | 執行時間 | 掃描行數 | 臨時表 | 適用版本 |
|---|---|---|---|---|
| 相關子查詢 | 12.4s | ~10億 | 是 | 全版本 |
| 用戶變量 | 0.3s | 100萬 | 否 | 5.7/8.0(需注意) |
| ROW_NUMBER() | 0.25s | 100萬 | 否 | 8.0+ |
-- 覆蓋索引,支援所有方案
CREATE INDEX idx_cat_date_id ON Article (Category, InsertDate DESC, id);
-- 方案二/三額外受益
-- ORDER BY Category, InsertDate DESC 可直接使用索引順序
-- 方法 1:MAX + JOIN(適用單欄位)
SELECT a.* FROM Article a
JOIN (
SELECT Category, MAX(InsertDate) AS max_date
FROM Article GROUP BY Category
) b ON a.Category = b.Category AND a.InsertDate = b.max_date;
-- 方法 2:NOT EXISTS 反模式
SELECT * FROM Article a
WHERE NOT EXISTS (
SELECT 1 FROM Article b
WHERE b.Category = a.Category
AND b.InsertDate > a.InsertDate
);
ROW_NUMBER(),標準、高效、可維護ORDER BY 位置(分組列, 排序列 DESC, 主鍵) 覆蓋索引