表 shop(article, dealer, price)，需求：每種商品（article）的最高價及對應經銷商（dealer）。
article | dealer | price
0001 | A | 3.45
0001 | B | 3.99 ← 最高
0002 | A | 10.99 ← 最高
0003 | B | 1.45
0003 | C | 1.69 ← 最高
0003 | D | 1.25
0004 | D | 19.95 ← 最高
| # | SQL | 原理 | MySQL 版本支持 | 備註 |
|---|---|---|---|---|
| 1 | SELECT article, MAX(price) FROM shop GROUP BY article | 僅聚合價格，無法直接取 dealer（MySQL ONLY_FULL_GROUP_BY 下報錯） | 所有 | 不完整 |
| 2 | SELECT article, MAX(price), dealer FROM shop GROUP BY article | MySQL 允許非聚合列，但 dealer 取值不確定（來自組內任意行） | 5.7 前 / 關閉 ONLY_FULL_GROUP_BY | 不可靠 |
| 3 | SELECT s1.article, s1.dealer, s1.price FROM shop s1 WHERE price = (SELECT MAX(s2.price) FROM shop s2 WHERE s1.article=s2.article) | 相關子查詢，語義精確 | 所有 | 索引 (article,price) 時較快 |
| 4 | 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 | 派生表 JOIN，可利用索引、無相關子查詢開銷 | 所有 | 推薦 |
| 5 | SELECT s1.* FROM shop s1 LEFT JOIN shop s2 ON s1.article=s2.article AND s1.price < s2.price WHERE s2.article IS NULL | 「無更高價」反向過濾 | 所有 | 性能一般 |
CREATE INDEX idx_article_price ON shop(article, price);SELECT article, dealer, price FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY article ORDER BY price DESC) rn FROM shop) t WHERE rn=1;ONLY_FULL_GROUP_BY=ON（5.7+ 默認開啟）下直接報錯。EXPLAIN 觀察 DEPENDENT SUBQUERY vs DERIVED。
