SQL 進階技巧：單一查詢取得各群組前 N 筆最大值（Top-N Per Group）

經典問題：依 qu 分組，取每組 je 最大的前 2 筆，限制只能用一條 SQL。本文提供三種相容性不同的寫法，適用 MySQL、SQL Server、Oracle 等主流資料庫。

測試資料

CREATE TABLE hard (qu CHAR(11), co CHAR(11), je NUMERIC(3,0));
INSERT INTO hard VALUES
('A','1',3), ('A','2',4), ('A','4',2), ('A','6',9),
('B','1',4), ('B','2',5), ('B','3',6),
('C','3',4), ('C','6',7), ('C','2',3);

期望結果

qu  co  je
A   6   9
A   2   4
B   3   6
B   2   5
C   6   7
C   3   4

寫法一：相關子查詢 + COUNT（通用標準 SQL）

SELECT * FROM hard a
WHERE (SELECT COUNT(*) FROM hard b
       WHERE a.qu = b.qu AND b.je >= a.je) <= 2
ORDER BY qu, je DESC;

原理：對每一列算「同組且 je 不小於自己」的筆數，若 ≤ 2 則為前二大。優點：純標準 SQL，所有資料庫通用。缺點：大資料量效能較差（O(N²)）。

寫法二：IN + TOP/LIMIT 子查詢（MySQL / SQL Server）

-- MySQL
SELECT * FROM hard a
WHERE je IN (SELECT je FROM hard b
             WHERE a.qu = b.qu
             ORDER BY je DESC LIMIT 2);

-- SQL Server
SELECT * FROM hard a
WHERE je IN (SELECT TOP 2 je FROM hard b
             WHERE a.qu = b.qu
             ORDER BY je DESC);

注意：若 je 有重複值，可能回傳超過 2 筆。嚴謹做法應用主鍵或 ROW_NUMBER()。

寫法三：視窗函數 ROW_NUMBER()（MySQL 8.0+ / PostgreSQL / Oracle / SQL Server 2005+）

WITH ranked AS (
    SELECT qu, co, je,
           ROW_NUMBER() OVER (PARTITION BY qu ORDER BY je DESC) AS rn
    FROM hard
)
SELECT qu, co, je FROM ranked WHERE rn <= 2
ORDER BY qu, je DESC;

優點：語意清晰、效能最佳、支援精確 Top-N、易擴展（如取前 3、前 10%、並列處理）。現代首選方案。

效能比較建議

索引建議

CREATE INDEX idx_hard_qu_je ON hard(qu, je DESC);

結語

「各群組 Top-N」是報表與儀表板常見需求。掌握視窗函數是現代 SQL 開發者的必備技能；舊版資料庫則善用相關子查詢或 TOP/LIMIT 子查詢。根據資料庫版本與資料量選擇最適解。