SQL 分組取每組最大值完整攻略：相關子查詢、JOIN、ROW_NUMBER() 三大經典模式對比

問題定義

給定表 lk1(uid, pid, price, time)，需查詢每個 pid 群組中 price 最高的完整記錄（若有並列最高，皆需返回）。

測試資料建立

CREATE TABLE lk1 (
    uid INT,
    pid INT,
    price INT,
    `time` DATE
) ENGINE=MyISAM;

INSERT INTO lk1 VALUES
(1, 1, 100, '2007-07-01'),
(1, 2, 150, '2007-07-02'),
(2, 1, 110, '2007-07-03'),
(3, 1, 120, '2007-07-04'),
(4, 2, 180, '2007-07-04'),
(3, 2, 170, '2007-07-04'),
(6, 3, 130, '2007-07-04');

方案一：相關子查詢（Correlated Subquery）

SELECT *
FROM lk1 l1
WHERE price = (
    SELECT MAX(l2.price)
    FROM lk1 l2
    WHERE l1.pid = l2.pid
);

• 優點：語意直觀、符合 ANSI SQL、並列最高值自動全部返回。
• 缺點：大資料量下可能效能較差（每行執行一次子查詢），需有 (pid, price) 複合索引支援。

方案二：內聯衍生表 JOIN（Inline Derived Table）

SELECT t.*
FROM lk1 t
JOIN (
    SELECT pid, MAX(price) AS max_price
    FROM lk1
    GROUP BY pid
) tm ON t.pid = tm.pid AND t.price = tm.max_price;

• 優點：子查詢只掃描一次、易於優化器轉換為 Hash/ Merge Join、效能穩定。
• 適用：大多數關聯式資料庫（MySQL、SQL Server、PostgreSQL、Oracle）。

方案三：視窗函數 ROW_NUMBER() / RANK()（現代標準）

-- 取並列最高用 RANK()
WITH ranked AS (
    SELECT *, RANK() OVER (PARTITION BY pid ORDER BY price DESC) AS rk
    FROM lk1
)
SELECT uid, pid, price, `time`
FROM ranked
WHERE rk = 1;

-- 僅取單一最高用 ROW_NUMBER()
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY pid ORDER BY price DESC, uid) AS rn
    FROM lk1
)
SELECT uid, pid, price, `time`
FROM ranked
WHERE rn = 1;

• 優點：單次掃描、語法清晰、可輕鬆擴展「取前 N 名」、「並列處理邏輯」。
• 支援：SQL Server 2005+、Oracle 8i+、PostgreSQL 8.4+、MySQL 8.0+、MariaDB 10.2+。

方案四：MySQL 專用「排序後 GROUP BY」技巧（不推薦生產環境）

SELECT * FROM (
    SELECT * FROM lk1 ORDER BY price DESC
) t
GROUP BY pid;

風險：依賴 MySQL 非標準行為（ONLY_FULL_GROUP_BY 關閉時才生效），結果不可預測，嚴禁用於生產系統。

效能對比與索引建議

總結建議

• **MySQL 8.0+ / SQL Server 2005+ / PostgreSQL / Oracle**：優先使用 RANK() 或 ROW_NUMBER()。
• **舊版 MySQL / 跨平台相容**：使用衍生表 JOIN 寫法。
• **避免** 使用 ORDER BY ... GROUP BY 非標準寫法。