MySQL GROUP BY 取各分組 Top N 完整解法

問題場景

表 person：

+--------+-------+-----+
| Person | Group | Age |
+--------+-------+-----+
| Bob    | 1     | 32  |
| Jill   | 1     | 34  |
| Shawn  | 1     | 42  |
| Jake   | 2     | 29  |
| Paul   | 2     | 36  |
| Laura  | 2     | 39  |
+--------+-------+-----+

需求：每組取 Age 最大的前 2 名

+--------+-------+-----+
| Shawn  | 1     | 42  |
| Jill   | 1     | 34  |
| Laura  | 2     | 39  |
| Paul   | 2     | 36  |
+--------+-------+-----+

解法一：用戶變數模擬 ROW_NUMBER（MySQL 5.x 相容）

SET @num := 0, @group := '';

SELECT person, `group`, age
FROM (
    SELECT 
        person, `group`, age,
        @num := IF(@group = `group`, @num + 1, 1) AS row_number,
        @group := `group` AS dummy
    FROM mytable
    ORDER BY `group`, age DESC, person
) AS x
WHERE x.row_number <= 2;

原理：按分組+排序欄位排序，變數記錄組內序號，外層過濾。

解法二：相關子查詢計數法（標準 SQL，效能較差）

SELECT a.person, a.group, a.age
FROM person AS a
WHERE (
    SELECT COUNT(*) 
    FROM person AS b 
    WHERE b.group = a.group 
      AND b.age >= a.age
) <= 2
ORDER BY a.group ASC, a.age DESC;

原理：統計同組中年齡不小於當前行的筆數，≤N 即為 Top N。大資料量下全表掃描嚴重，不推薦生產環境。

解法三：MySQL 8.0+ 窗口函數（現代標準寫法）

WITH ranked AS (
    SELECT 
        person, `group`, age,
        ROW_NUMBER() OVER (PARTITION BY `group` ORDER BY age DESC, person) AS rn
    FROM mytable
)
SELECT person, `group`, age
FROM ranked
WHERE rn <= 2;

優點：語法清晰、效能最優、標準相容。MySQL 8.0、MariaDB 10.2+ 全支援。

效能對比與選擇建議

建議：MySQL 8.0+ 必用窗口函數；舊版本用變數法並建立 (group, age DESC) 複合索引。