MySQL 分組取 Top-N 實戰：用戶變數實現各群組前 3 筆資料查詢

MySQL 分組取 Top-N 實戰：用戶變數實現各群組前 3 筆資料查詢

常見需求：依某欄位分組，每組只取前 N 筆（如各平台熱門商品 Top 3、各部門薪資前 5）。MySQL 8.0 前無 ROW_NUMBER() OVER (PARTITION BY ...) 窗口函數，傳統做法是利用用戶變數模擬行號。本文以產品表為例，完整演示建表、資料、SQL 與原理解析。

1. 問題與資料準備

表 p_a：id、ptid(平台 ID)、name(產品名)。

CREATE TABLE p_a (
  id INT,
  ptid VARCHAR(20),
  name VARCHAR(20)
);

INSERT INTO p_a VALUES
(1,'100','產品1'), (2,'100','產品2'), (3,'100','產品3'),
(4,'100','產品4'), (5,'100','產品5'), (6,'100','產品6'),
(7,'101','產品7'), (8,'101','產品8'), (9,'101','產品9'),
(10,'101','產品10'), (11,'101','產品11');

目標：每個 ptid 取前 3 筆（順序不限）。

2. 核心 SQL：用戶變數打行號

SELECT * FROM (
  SELECT
    a.*,
    CASE WHEN @old_ptid = ptid THEN @rownum := @rownum + 1 ELSE @rownum := 1 END AS rn,
    @old_ptid := ptid
  FROM p_a a
  CROSS JOIN (SELECT @rownum := 0, @old_ptid := '') r
) t
WHERE rn <= 3;

3. 執行流程圖解

1. CROSS JOIN 初始化兩個變數：@rownum=0（組內序號）、@old_ptid=''（上一列的分組值）。
2. 依表掃描順序逐列處理：
3. 若 ptid = @old_ptid → 同組，@rownum++；
4. 否則 → 新組，@rownum=1；
5. 更新 @old_ptid := ptid 供下一列比較。
6. 外層 WHERE rn <= 3 過濾保留每組前 3 列。

4. 結果

id | ptid | name   | rn
---|------|--------|---
1  | 100  | 產品1  | 1
2  | 100  | 產品2  | 2
3  | 100  | 產品3  | 3
7  | 101  | 產品7  | 1
8  | 101  | 產品8  | 2
9  | 101  | 產品9  | 3

5. 關鍵注意事項

• 順序敏感：子查詢 必須依分組欄位排序（ORDER BY ptid, id），否則同組資料若不連續會導致行號錯亂。生產環境請在子查詢加 ORDER BY ptid, id。
• CROSS JOIN (SELECT @rownum:=0, @old_ptid:='') r 寫法確保變數在查詢開始前初始化，避免會話殘留值干擾。
• MySQL 8.0+ 建議改用標準窗口函數：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ptid ORDER BY id) AS rn
  FROM p_a
) t WHERE rn <= 3;

6. 擴展：各組取最新/最高值

若需每組取 create_time 最新 3 筆，子查詢加 ORDER BY ptid, create_time DESC 即可，邏輯完全通用。

用戶變數模擬行號是 MySQL 5.6/5.7 時代分組 Top-N 的標準解法，理解其「掃描順序決定一切」的本質，能靈活應對各類分組取樣需求。