MySQL 分組取 Top N 完整方案集：排名、去重、Group By 與 Order By 執行順序深度解析

「每組取前 N 筆」是高頻需求。本文提供 7 類場景的標準 SQL 寫法，並深度剖析 GROUP BY 與 ORDER BY 的執行順序陷阱。

測試資料

name | val | memo
a    | 2   | a2
a    | 1   | a1
a    | 3   | a3
b    | 1   | b1
b    | 3   | b3
b    | 2   | b2
b    | 4   | b4
b    | 5   | b5

核心場景與標準寫法

1. 每組取最大值所在行

-- 方法 1：子查詢關聯（推薦，直觀）
SELECT a.* FROM tb a
JOIN (SELECT name, MAX(val) val FROM tb GROUP BY name) b
  ON a.name = b.name AND a.val = b.val;

-- 方法 2：NOT EXISTS（效能略差）
SELECT a.* FROM tb a
WHERE NOT EXISTS (SELECT 1 FROM tb WHERE name = a.name AND val > a.val);

2. 每組取最小值所在行

將 MAX 改 MIN、> 改 < 即可。

3. 每組取第一筆（依插入順序/無序）

SELECT a.* FROM tb a
WHERE val = (SELECT TOP 1 val FROM tb WHERE name = a.name);

4. 每組隨機取一筆

SELECT a.* FROM tb a
WHERE val = (SELECT TOP 1 val FROM tb WHERE name = a.name ORDER BY NEWID());

5. 每組取最小 N 筆（N=2）

SELECT a.* FROM tb a
WHERE 2 > (SELECT COUNT(*) FROM tb WHERE name = a.name AND val < a.val)
ORDER BY a.name, a.val;

6. 每組取最大 N 筆

將 < 改 >、排序改 DESC。

7. 整行重複資料去重下的 Top N

同場景 5/6，配合 ROW_NUMBER() 窗口函數（MySQL 8.0+）更優雅：

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY val) rn
  FROM tb
)
SELECT * FROM ranked WHERE rn <= 2;

Group By 與 Order By 執行順序陷阱

錯誤認知

SELECT * FROM test GROUP BY category_id ORDER BY `date`;
-- 以為會取各分類最新文章

真實執行順序

1. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

GROUP BY 執行時將資料分組，**每組僅保留第一筆**（非排序後的第一筆），ORDER BY 僅能對這些「已分組的第一筆」排序，無法取得各組最新資料。

正確解法（MySQL 5.7/8.0 通用）

方案一：子查詢先排序再分組

SELECT * FROM (
  SELECT * FROM `test` ORDER BY `date` DESC
) t GROUP BY category_id;

方案二：GROUP_CONCAT + 子查詢（舊版本相容）

SELECT * FROM `test`
WHERE id IN (
  SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY `date` DESC), ',', 1)
  FROM `test` GROUP BY category_id
);

方案三：窗口函數（MySQL 8.0+ 推薦）

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY `date` DESC) rn
  FROM `test`
)
SELECT * FROM ranked WHERE rn = 1;

效能建議

• 大數據量優先用 JOIN 衍生表或窗口函數
• 為分組欄位 + 排序欄位建立複合索引：INDEX (category_id, date DESC)
• 避免在 HAVING 中使用子查詢，改用衍生表