表 Article(Id, Category, InsertDate),求**每個分類下最新的 N 篇文章**(按 InsertDate 降序)。
SELECT A1.*
FROM Article A1
INNER JOIN (
SELECT A.Category, A.InsertDate
FROM Article A
LEFT JOIN Article B
ON A.Category = B.Category
AND A.InsertDate <= B.InsertDate
GROUP BY A.Category, A.InsertDate
HAVING COUNT(B.InsertDate) <= 3 -- N=3
) B1 ON A1.Category = B1.Category AND A1.InsertDate = B1.InsertDate
ORDER BY A1.Category, A1.InsertDate DESC;
INSERTDATE 有重複值時會多返回行;大表自連接極慢 (O(N²))。SELECT Id, Category, InsertDate
FROM (
SELECT
Id, Category, InsertDate,
@rn := IF(@prev_cat = Category, @rn + 1, 1) AS rn,
@prev_cat := Category
FROM Article
CROSS JOIN (SELECT @rn := 0, @prev_cat := '') vars
ORDER BY Category, InsertDate DESC
) t
WHERE rn <= 3;
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 <= 3
ORDER BY Category, InsertDate DESC;
RANK()/DENSE_RANK() 可選)。PARTITION BY Category, Author 多維分組。SELECT * FROM Article a1
WHERE (
SELECT COUNT(*) FROM Article a2
WHERE a2.Category = a1.Category
AND a2.InsertDate >= a1.InsertDate
) <= 3
ORDER BY Category, InsertDate DESC;
-- 1. 先查所有分類
SELECT DISTINCT Category FROM Article;
-- 2. 應用層循環每個分類查 Top N(可並行)
SELECT * FROM Article WHERE Category = ? ORDER BY InsertDate DESC LIMIT 3;
| 資料量 | MySQL 版本 | 推薦方案 | 預估耗時 |
|---|---|---|---|
| < 10 萬 | 任意 | 方案三 (CTE) / 方案二 | < 100ms |
| 10 萬 ~ 1000 萬 | 5.7 | 方案二 (變量) | ~ 1-5s |
| 10 萬 ~ 1000 萬 | 8.0+ | 方案三 (CTE+窗口) | ~ 500ms-2s |
| > 1000 萬 | 8.0+ | 方案五 (應用層分批) + 覆蓋索引 | 分批並行 < 1s |
-- 覆蓋索引:WHERE + ORDER BY + SELECT 欄位全覆蓋
CREATE INDEX idx_cat_date_id ON Article (Category, InsertDate DESC, Id);
Category 先行,滿足分組與篩選。InsertDate DESC 避免 filesort,直接按索引順序取 Top N。Id 覆蓋查詢,回表為零。**MySQL 8.0+ 首選 CTE + ROW_NUMBER()**,標準、高效、可維護。5.7 用戶變量模擬次之。大表務必建立 (Category, InsertDate DESC, Id) 覆蓋索引。極大資料量下,應用層分批查詢配合緩存是終極方案。