MySQL 經典查詢案例精講：從最大值到動態行轉列

MySQL 經典查詢案例精講：從最大值到動態行轉列

MySQL 官方文檔「Common Queries」章節收錄的經典案例，涵蓋聚合查詢、分組統計、關聯子查詢、用戶變量等核心技巧。本文以 shop 表為例，逐步拆解 6 大類經典查詢模式，並延伸至動態行轉列實戰。

一、 基礎環境準備

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);

數據含義：商品編號、經銷商、價格。主鍵 (article, dealer) 保證同一商品不同經銷商價格唯一。

二、 經典查詢模式拆解

1. 列的最大值：MAX() 聚合

SELECT MAX(article) AS article FROM shop;
-- 結果：4

2. 擁有最大值的行：三種寫法對比

場景：查詢最貴商品的編號、經銷商、價格

-- 寫法 A：子查詢（最直觀）
SELECT article, dealer, price 
FROM shop 
WHERE price = (SELECT MAX(price) FROM shop);

-- 寫法 B：LEFT JOIN 自關聯（標準 SQL 模式）
SELECT s1.article, s1.dealer, s1.price 
FROM shop s1 
LEFT JOIN shop s2 ON s1.price < s2.price 
WHERE s2.article IS NULL;

-- 寫法 C：ORDER BY + LIMIT（MySQL 特有，性能最優）
SELECT article, dealer, price 
FROM shop 
ORDER BY price DESC LIMIT 1;

⚠️ 注意：若有多條並列最大值，LIMIT 1 僅返回一行；子查詢/JOIN 返回所有並列行。

3. 分組最大值：GROUP BY 聚合

SELECT article, MAX(price) AS price 
FROM shop 
GROUP BY article;
-- 結果：每個商品的最高價格

4. 分組最大值對應的完整行：四種方案

場景：每個商品的最高價格及對應經銷商

-- 方案 1：相關子查詢（易寫、效率低）
SELECT article, dealer, price 
FROM shop s1 
WHERE price = (SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);

-- 方案 2：非相關子查詢 + 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：LEFT JOIN 自關聯（標準 SQL）
SELECT s1.article, s1.dealer, s1.price 
FROM shop s1 
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price 
WHERE s2.article IS NULL;

-- 方案 4：用戶變量模擬 ROW_NUMBER（MySQL 8.0 前最優）
SELECT article, dealer, price FROM (
    SELECT 
        article, dealer, price,
        @rn := IF(@prev = article, @rn + 1, 1) AS rn,
        @prev := article
    FROM shop, (SELECT @prev := NULL, @rn := 0) vars
    ORDER BY article, price DESC
) t WHERE rn = 1;

💡 MySQL 8.0+ 直接使用窗口函數：ROW_NUMBER() OVER (PARTITION BY article ORDER BY price DESC)。

5. 用戶自定義變量應用

-- 累計求和
SELECT 
    article, dealer, price,
    @running_total := @running_total + price AS cumulative_sum
FROM shop, (SELECT @running_total := 0) vars
ORDER BY article, dealer;

-- 分組排名
SELECT 
    article, dealer, price,
    @rank := IF(@prev_article = article, @rank + 1, 1) AS rank,
    @prev_article := article
FROM shop, (SELECT @prev_article := '', @rank := 0) vars
ORDER BY article, price DESC;

三、 進階實戰：動態行轉列（Pivot Table）

場景：學生-課程-成績三表，需輸出「學生為行、課程為列、成績為值」的橫表。

1. 表結構與測試數據

CREATE TABLE student (stuid VARCHAR(16) PRIMARY KEY, stunm VARCHAR(20));
CREATE TABLE courses (courseno VARCHAR(20) PRIMARY KEY, coursenm VARCHAR(100));
CREATE TABLE score (stuid VARCHAR(16), courseno VARCHAR(20), scores FLOAT, PRIMARY KEY(stuid, courseno));

-- 插入 6 學生 × 7 課程 = 42 條成績記錄

2. 靜態行轉列（課程固定時）

SELECT 
    st.stuid, st.stunm,
    MAX(CASE c.coursenm WHEN '大學語文' THEN s.scores ELSE 0 END) AS '大學語文',
    MAX(CASE c.coursenm WHEN '線性代數' THEN IFNULL(s.scores,0) ELSE 0 END) AS '線性代數',
    -- ... 手寫每門課程
FROM student st
LEFT JOIN score s ON st.stuid = s.stuid
LEFT JOIN courses c ON c.courseno = s.courseno
GROUP BY st.stuid, st.stunm;

3. 動態行轉列：SQL 動態構造 + Prepared Statement

-- 步驟 1：動態生成 SELECT 列表
SELECT GROUP_CONCAT(DISTINCT CONCAT(
    'MAX(IF(c.coursenm = ''', c.coursenm, ''', s.scores, 0)) AS ''', c.coursenm, ''''
)) INTO @sql_columns FROM courses c;

-- 步驟 2：拼裝完整 SQL
SET @sql = CONCAT(
    'SELECT st.stuid, st.stunm, ', @sql_columns,
    ' FROM student st LEFT JOIN score s ON st.stuid = s.stuid',
    ' LEFT JOIN courses c ON c.courseno = s.courseno',
    ' GROUP BY st.stuid, st.stunm'
);

-- 步驟 3：準備並執行
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

4. 封裝為存儲過程（支持條件篩選）

DELIMITER $$
CREATE PROCEDURE `SP_QueryScore`(IN p_stuid VARCHAR(16))
READS SQL DATA
BEGIN
    DECLARE v_sql_columns TEXT DEFAULT NULL;
    DECLARE v_sql TEXT DEFAULT NULL;
    
    SELECT GROUP_CONCAT(DISTINCT CONCAT(
        'MAX(IF(c.coursenm = ''', c.coursenm, ''', s.scores, 0)) AS ''', c.coursenm, ''''
    )) INTO v_sql_columns FROM courses c;
    
    SET v_sql = CONCAT(
        'SELECT st.stuid, st.stunm, ', v_sql_columns,
        ' FROM student st LEFT JOIN score s ON st.stuid = s.stuid',
        ' LEFT JOIN courses c ON c.courseno = s.courseno'
    );
    
    IF p_stuid IS NOT NULL AND p_stuid <> '' THEN
        SET v_sql = CONCAT(v_sql, ' WHERE st.stuid = \'', p_stuid, '\'');
    END IF;
    
    SET v_sql = CONCAT(v_sql, ' GROUP BY st.stuid, st.stunm');
    
    PREPARE stmt FROM v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 調用
CALL SP_QueryScore('1001');  -- 單學生
CALL SP_QueryScore('');      -- 全部學生

關鍵點：

• GROUP_CONCAT 動態生成列表達式
• PREPARE/EXECUTE 實現動態 SQL 執行
• 存儲過程中使用 IF ... THEN ... END IF 流程控制（非 Java 風格大括號）
• 參數注入防禦：此處為內部調用，若對外暴露需嚴格驗證/轉義

四、 總結與避坑指南

核心心法：靜態 SQL 寫死，動態 SQL 構造。掌握 GROUP_CONCAT、PREPARE、用戶變量、窗口函數四大神器，應對 90% 複雜報表查詢需求。