MySQL 自連接查詢：分組取 Top-N 記錄的高效寫法

業務常見需求：按分類取每組前 N 筆記錄（如每類商品最新 2 條、每用戶最近 3 筆訂單）。本文通過實戰案例演示相關子查詢 + 計數的通用解法。

場景與樣本資料

CREATE TABLE t_info (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(24),   -- 分組鍵：'a' 或 'b'
  number INT          -- 排序鍵
) ENGINE=InnoDB;

INSERT INTO t_info VALUES
(1,'a',18),(2,'b',18),(3,'a',17),(4,'a',19),(5,'a',20),
(6,'b',30),(7,'b',31),(8,'b',32),(9,'a',25),(10,'b',26);

目標

按 name 分組，number 降序，取每組前 2 筆，結果按 name 排序。

錯誤嘗試

-- 僅排序無法限制每組筆數
SELECT * FROM t_info ORDER BY name, number DESC;

正確寫法：相關子查詢計數法

SELECT * FROM t_info a
WHERE 2 > (
  SELECT COUNT(*) FROM t_info b
  WHERE b.name = a.name AND b.number > a.number
)
ORDER BY a.name, a.number DESC;

邏輯解析

1. 外層遍歷表 a 每一行。
2. 子查詢統計：同組（name 相同）且 number 更大的行數。
3. 若該計數 小於 N（此處 2），說明該行在該組排名前 N，保留。
4. 外層 ORDER BY 保證輸出順序。

執行結果

+----+------+--------+
| id | name | number |
+----+------+--------+
|  5 | a    |     20 |
|  9 | a    |     25 |
|  7 | b    |     31 |
|  8 | b    |     32 |
+----+------+--------+

性能優化建議

• 建立複合索引 (name, number)，支援子查詢的範圍計數與排序。
• 大表（>100萬）考慮變量法或窗口函數（MySQL 8.0+ ROW_NUMBER() OVER (PARTITION BY name ORDER BY number DESC)）。
• 避免 JOIN 產生笛卡爾積再分組，效率遠低於相關子查詢。

MySQL 8.0+ 現代寫法

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY number DESC) AS rn
  FROM t_info
)
SELECT id, name, number FROM ranked WHERE rn <= 2 ORDER BY name, number DESC;

窗口函數可讀性強、優化器支援好，推薦升級至 8.0 後優先使用。