MySQL 常見查詢範例集：最大值、分組統計、外鍵聯結與 MAX-CONCAT 技巧

範例資料準備

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 article FROM shop;
-- 結果: 4

2. 擁有最大值的完整列 (標準 SQL 子查詢 vs MySQL 變通)

標準寫法 (需子查詢支援)：

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

MySQL 早期版本變通 (兩步驟)：

# 步驟 1 取得最大值
SELECT MAX(price) FROM shop;  -- 19.95
# 步驟 2 帶入查詢
SELECT article, dealer, price FROM shop WHERE price = 19.95;

MySQL 專用 LIMIT 寫法 (單筆)：

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

注意：若有多筆相同最高價，LIMIT 僅回傳其中一筆。

3. 分組最大值 (僅數值)

SELECT article, MAX(price) AS price
FROM shop
GROUP BY article;
-- 結果: (1,3.99), (2,10.99), (3,1.69), (4,19.95)

4. 分組最大值對應的完整列 (含 dealer)

臨時表法 (通用、效能佳)：

CREATE TEMPORARY TABLE tmp (
    article INT(4) UNSIGNED ZEROFILL NOT NULL,
    price   DOUBLE(16,2) NOT NULL
);
LOCK TABLES shop READ;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT shop.article, shop.dealer, shop.price
FROM shop, tmp
WHERE shop.article = tmp.article AND shop.price = tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;

MAX-CONCAT 駭客技巧 (單查詢、效能較差)：

SELECT article,
    SUBSTRING(MAX(CONCAT(LPAD(price,6,'0'), dealer)), 7) AS dealer,
    0.00 + LEFT(MAX(CONCAT(LPAD(price,6,'0'), dealer)), 6) AS price
FROM shop
GROUP BY article;

原理：將價格左補零成定寬、連接 dealer，MAX 取最大字串 (字典序等同數值序)，再拆解。客戶端需自行分割欄位。

5. 外鍵聯結範例 (無強制約束)

CREATE TABLE persons (
    id   SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name CHAR(60) NOT NULL
);

CREATE TABLE shirts (
    id     SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    style  ENUM('t-shirt','polo','dress') NOT NULL,
    color  ENUM('red','blue','orange','white','black') NOT NULL,
    owner  SMALLINT UNSIGNED NOT NULL,  -- 參考 persons.id
    INDEX (owner)
);

INSERT INTO persons VALUES (NULL, 'Antonio Paz');
INSERT INTO shirts VALUES
(NULL,'polo','blue',LAST_INSERT_ID()),
(NULL,'dress','white',LAST_INSERT_ID()),
(NULL,'t-shirt','blue',LAST_INSERT_ID());

INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska');
INSERT INTO shirts VALUES
(NULL,'dress','orange',LAST_INSERT_ID()),
(NULL,'polo','red',LAST_INSERT_ID()),
(NULL,'dress','blue',LAST_INSERT_ID()),
(NULL,'t-shirt','white',LAST_INSERT_ID());

-- 查詢 Lilliana 的非白色襯衫
SELECT s.* FROM persons p, shirts s
WHERE p.name LIKE 'Lilliana%' AND s.owner = p.id AND s.color <> 'white';

MySQL 不強制外鍵完整性 (除 InnoDB 並明確定義 FK)，應用層需自行維護參考一致性。

關鍵技巧總結

• 子查詢不可用時：臨時表、多步驟、LIMIT、MAX-CONCAT。
• LAST_INSERT_ID() 取得剛插入的 AUTO_INCREMENT 值，安全於併發環境。
• ENUM 適用固定集合欄位，儲存緊湊且自動驗證合法值。
• 外鍵邏輯建議在應用層或 InnoDB 明確定義 FOREIGN KEY 維護。