SQL 分組取每組前 N 行：Top-N 查詢多種寫法與效能對比

SQL 分組取每組前 N 行：Top-N 查詢多種寫法與效能對比

「每個分類下價格最低的商品」、「每個用戶最近 5 條訂單」、「每個玩家最高分」——這類 **Top-N per Group** 問題在業務中極常見。本文以水果價格表為例，詳解 5 種主流寫法、適用場景與效能特徵。

測試數據：fruits 表

type    | variety | price
--------|---------|------
apple   | gala    | 2.5
apple   | fuji    | 3.0
orange  | navel   | 3.5
orange  | valencia| 4.0
pear    | bartlett| 2.0
cherry  | bing    | 5.0
cherry  | rainier | 6.0

目標：每類水果取價格最低的 2 行（Top 2）

方法 1：自連接（經典寫法）

SELECT f.type, f.variety, f.price
FROM fruits f
INNER JOIN (
  SELECT type, MIN(price) AS min_price
  FROM fruits
  GROUP BY type
) x ON f.type = x.type AND f.price = x.min_price;

缺點：僅能取最小值（Top 1），擴展到 Top N 需疊加子查詢，SQL 膨脹嚴重。

方法 2：相關子查詢（直觀但低效）

SELECT type, variety, price
FROM fruits f1
WHERE price = (
  SELECT MIN(price) FROM fruits f2 WHERE f2.type = f1.type
);

Top 2 寫法：

SELECT type, variety, price
FROM fruits f1
WHERE price = (SELECT MIN(price) FROM fruits f2 WHERE f2.type = f1.type)
   OR price = (
     SELECT MIN(price) FROM fruits f3 
     WHERE f3.type = f1.type 
     AND price > (SELECT MIN(price) FROM fruits f4 WHERE f4.type = f1.type)
   );

缺點：N 越大 SQL 指數級膨脹，執行計劃差，O(N²) 複雜度，不推薦生產使用。

方法 3：計數法（通用、優雅、O(N)）

SELECT type, variety, price
FROM fruits f1
WHERE (
  SELECT COUNT(*) 
  FROM fruits f2 
  WHERE f2.type = f1.type 
  AND f2.price <= f1.price
) <= 2;  -- 這裡改數字即可支援 Top N

原理：對外層每行，統計同組內「價格 ≤ 當前行」的行數，若 ≤ N 則屬於 Top N。
優點：單條 SQL 支援任意 N，邏輯清晰。
缺點：相關子查詢在某些優化器下執行計劃不佳，大數據量需測試。

方法 4：UNION ALL 分組限制（有索引時極快）

(SELECT * FROM fruits WHERE type = 'apple'  ORDER BY price LIMIT 2)
UNION ALL
(SELECT * FROM fruits WHERE type = 'orange' ORDER BY price LIMIT 2)
UNION ALL
(SELECT * FROM fruits WHERE type = 'pear'   ORDER BY price LIMIT 2)
UNION ALL
(SELECT * FROM fruits WHERE type = 'cherry' ORDER BY price LIMIT 2);

適用條件：

• 分組值已知且固定（如狀態碼、類型碼）
• (type, price) 上有複合索引
• 分組數較少（< 20）

關鍵點：必須用 UNION ALL 而非 UNION，避免額外去重排序開銷。

方法 5：用戶變量編號（MySQL 專用、單掃描 O(N)）

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

SELECT type, variety, price
FROM (
  SELECT 
    type, variety, price,
    @num := IF(@type = type, @num + 1, 1) AS row_number,
    @type := type AS dummy
  FROM fruits
  ORDER BY type, price
) x
WHERE x.row_number <= 2;

執行流程

1. 子查詢按 type, price 排序
2. 掃描時：同組遞增 @num，換組重置為 1
3. 外層過濾 row_number <= 2

優勢：單次全表掃描，無相關子查詢，O(N) 線性複雜度，大數據量效能最優。
注意：MySQL 8.0+ 建議改用窗口函數 ROW_NUMBER() OVER (PARTITION BY type ORDER BY price)。

效能對比總結

MySQL 8.0+ 現代標準寫法

WITH ranked AS (
  SELECT 
    type, variety, price,
    ROW_NUMBER() OVER (PARTITION BY type ORDER BY price) AS rn
  FROM fruits
)
SELECT type, variety, price
FROM ranked
WHERE rn <= 2;

優勢：標準 SQL、可讀性強、優化器支援最好、支援複雜分區排序。

選型建議

• MySQL 8.0+：**首選窗口函數**
• MySQL 5.7 / 大表動態分組：**用戶變量編號法**
• 固定少量分組 + 索引：**UNION ALL + LIMIT**
• 簡單 Top 1 / 快速原型：**自連接或計數法**
• 避免：多層疊加的相關子查詢

掌握多種寫法並理解其效能特徵，能根據業務數據量、MySQL 版本、索引狀況做出最優選擇。