範例資料準備

CREATE TABLE shop (
  article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
  dealer  CHAR(20) DEFAULT '' NOT NULL,
  price   DOUBLE(16,2) DEFAULT '0.00' 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 MAX(article) AS max_article FROM shop;
-- 結果：4

2. 擁有最大值的完整行

子查詢法

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

排序 + LIMIT 法（效能較好）

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

3. 分組最大值（每組最高價）

SELECT article, MAX(price) AS max_price
FROM shop
GROUP BY article;
-- 結果：
-- 0001  3.99
-- 0002  10.99
-- 0003  1.69
-- 0004  19.95

4. 分組內最大值對應的完整行

相關子查詢

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

JOIN 法（大數據量較快）

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

5. 用戶變量找極值行

SELECT @min_price := MIN(price), @max_price := MAX(price) FROM shop;
SELECT * FROM shop WHERE price = @min_price OR price = @max_price;
-- 結果：最便宜 0003/D/1.25，最貴 0004/D/19.95

6. 多鍵值 OR 查詢優化

問題

SELECT * FROM test_table
WHERE field1_index = 'val' OR field2_index = 'val';

MySQL 無法同時使用兩個索引，會退化為全表掃描。

UNION 改寫（利用索引合併）

SELECT field1_index, field2_index FROM test_table WHERE field1_index = 'val'
UNION
SELECT field1_index, field2_index FROM test_table WHERE field2_index = 'val';

UNION 預設去重，若確定無重複可用 UNION ALL 更快。

7. 進階實用查詢模式

分頁查詢（大偏移量優化）

-- 延遲關聯法
SELECT t.* FROM (
  SELECT id FROM large_table ORDER BY id LIMIT 100000, 20
) AS d JOIN large_table t ON t.id = d.id;

查找重複記錄

SELECT email, COUNT(*) cnt FROM users GROUP BY email HAVING cnt > 1;

刪除重複保留一條

DELETE t1 FROM users t1
JOIN users t2 ON t1.email = t2.email AND t1.id > t2.id;

間隙查詢（找出缺失的 ID）

SELECT a.id + 1 AS start, MIN(b.id) - 1 AS end
FROM sequence a
JOIN sequence b ON a.id < b.id
GROUP BY a.id
HAVING start <= MIN(b.id) - 1;

總結

掌握子查詢、JOIN、用戶變量、UNION 等技巧，能靈活解決「取極值行、分組 Top-N、多條件索引優化」等常見需求。實際專案中請配合 EXPLAIN 分析執行計劃，選擇最優寫法。