MySQL 查詢各群組最大值對應完整資料：四種 SQL 寫法效能比較

場景：shop(article, dealer, price)，每種圖書 (article) 找出最高價格的經銷商 (dealer)。

測試資料

CREATE TABLE shop (
  article INT(4) UNSIGNED ZEROFILL NOT NULL,
  dealer CHAR(20) NOT NULL,
  price DOUBLE(16,2) NOT NULL,
  PRIMARY KEY (article, dealer)
);
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

四種寫法比較

1. 僅取最大價 (無經銷商)

SELECT article, MAX(price) FROM shop GROUP BY article;

2. MySQL 非標準 GROUP BY (取任意經銷商，可能錯誤)

SELECT article, MAX(price), dealer FROM shop GROUP BY article;
-- 結果 dealer 可能非最高價對應 (如 article=1 顯示 A 而非 B)

3. 子查詢關聯 (標準、正確)

SELECT article, dealer, price FROM shop s1
WHERE price = (SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);

4. 衍生表 JOIN (標準、效能通常優於子查詢)

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
JOIN (
  SELECT article, MAX(price) AS price FROM shop GROUP BY article
) s2 ON s1.article = s2.article AND s1.price = s2.price;

結果驗證

寫法 3、4 正確輸出：
1 B 3.99, 2 A 10.99, 3 C 1.69, 4 D 19.95

效能建議

• 小表：子查詢可讀性佳
• 大表：衍生表 JOIN 通常更快 (可用索引、避免逐行執行子查詢)
• MySQL 8.0+ 可用窗口函數 ROW_NUMBER() OVER (PARTITION BY article ORDER BY price DESC) 最優雅