您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 動態行轉列完整實戰：GROUP_CONCAT 構造列、Prepared Statement 執行、存儲過程封裝與條件篩選

編輯：MySQL 綜合教學

MySQL 動態行轉列完整實戰：GROUP_CONCAT 構造列、Prepared Statement 執行、存儲過程封裝與條件篩選
報表系統常見需求：行轉列（Pivot），但列名（如課程、月份、門店）動態變化。本文以「學生-課程-成績」經典模型，完整演示從靜態行轉列到動態構造、Prepared Statement 執行、存儲過程封裝條件篩選的全鏈路實現。

一、核心表結構與測試數據

CREATE TABLE student (
    stuid VARCHAR(16) PRIMARY KEY COMMENT '學號',
    stunm VARCHAR(20) NOT NULL COMMENT '姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE courses (
    courseno VARCHAR(20) PRIMARY KEY COMMENT '課程號',
    coursenm VARCHAR(100) NOT NULL COMMENT '課程名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE score (
    stuid VARCHAR(16) NOT NULL,
    courseno VARCHAR(20) NOT NULL,
    scores FLOAT DEFAULT NULL,
    PRIMARY KEY (stuid, courseno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

二、靜態行轉列（列固定時）

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;

缺點：課程增減需修改 SQL，不可維護。

三、動態行轉列三步走

步驟 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;

輸出示例：

MAX(IF(c.coursenm = '大學語文', s.scores, 0)) AS '大學語文',
MAX(IF(c.coursenm = '線性代數', s.scores, 0)) AS '線性代數',
...

步驟 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;

四、直接拼接 SQL 的陷阱

-- 錯誤示範：子查詢返回字符串，非列表達式
SELECT st.stuid, st.stunm, (
    SELECT GROUP_CONCAT(...) FROM courses c
) FROM student st ... 
-- 結果：所有動態列擠在一列字符串中，非預期

原因：MySQL 不支持「子查詢展開為列列表」，必須通過 PREPARE 動態執行完整語句。

五、封裝存儲過程：支持條件篩選

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;
    
    -- 1. 動態生成列
    SELECT GROUP_CONCAT(DISTINCT CONCAT(
        'MAX(IF(c.coursenm = ''', c.coursenm, ''', s.scores, 0)) AS ''', c.coursenm, ''''
    )) INTO v_sql_columns FROM courses c;
    
    -- 2. 拼裝基礎 SQL
    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'
    );
    
    -- 3. 條件篩選（存儲過程中可用 IF 流程控制）
    IF p_stuid IS NOT NULL AND p_stuid <> '' THEN
        SET v_sql = CONCAT(v_sql, ' WHERE st.stuid = \'', p_stuid, '\'');
    END IF;
    
    -- 4. 分組
    SET v_sql = CONCAT(v_sql, ' GROUP BY st.stuid, st.stunm');
    
    -- 5. 執行
    PREPARE stmt FROM v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

關鍵點：

存儲過程中 IF ... THEN ... END IF 無大括號，類自然語言
參數注入防禦：內部調用相對安全；對外暴露需 QUOTE() 或應用層驗證
READS SQL DATA 特性聲明，優化器可優化

六、調用驗證

-- 查詢單個學生
CALL SP_QueryScore('1001');
-- 查詢所有學生
CALL SP_QueryScore('');
-- 查詢不存在學生
CALL SP_QueryScore('9999'); -- 返回空結果集

七、進階擴展：多維度動態透視

-- 支持按學期、年級、班級動態透視
CREATE PROCEDURE `SP_PivotScore`(
    IN p_term VARCHAR(20),
    IN p_grade VARCHAR(20),
    IN p_class VARCHAR(20)
)
READS SQL DATA
BEGIN
    DECLARE v_cols TEXT;
    DECLARE v_sql TEXT;
    
    -- 動態列：課程 + 統計維度 (AVG/MAX/MIN/COUNT)
    SELECT GROUP_CONCAT(DISTINCT CONCAT(
        'MAX(IF(c.coursenm = ''', c.coursenm, ''', ', 
        CASE WHEN p_stat_type = 'avg' THEN 'AVG' ELSE 'MAX' END,
        '(s.scores)), 0)) AS ''', c.coursenm, ''''
    )) INTO v_cols FROM courses c;
    
    SET v_sql = CONCAT('SELECT ', v_cols, ' FROM ... WHERE 1=1 ');
    IF p_term IS NOT NULL THEN SET v_sql = CONCAT(v_sql, ' AND term = ', QUOTE(p_term)); END IF;
    IF p_grade IS NOT NULL THEN SET v_sql = CONCAT(v_sql, ' AND grade = ', QUOTE(p_grade)); END IF;
    IF p_class IS NOT NULL THEN SET v_sql = CONCAT(v_sql, ' AND class = ', QUOTE(p_class)); END IF;
    
    PREPARE stmt FROM v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

八、避坑指南與最佳實踐

坑點	後果	修正
`GROUP_CONCAT` 截斷 (默認 1024 字節)	動態列不全	`SET SESSION group_concat_max_len = 1000000;`
列名含特殊字符 (空格、引號、關鍵字)	SQL 語法錯誤	CONCAT('`', REPLACE(coursenm, '`', '``'), '`') 反引號包裹並轉義
無數據時動態列為空	`PREPARE` 失敗	預設 `IFNULL(v_cols, '1 AS dummy')`
大結果集 OOM	服務器崩潰	分頁/流式處理、限制透視列數
並發下 Prepared Statement 泄漏	Prepared 句柄耗盡	嚴格 `DEALLOCATE PREPARE`、連接池配置 `max_prepared_stmt_count`

九、總結

MySQL 動態行轉列核心技術棧：**GROUP_CONCAT 構造列表達式 + PREPARE/EXECUTE 動態執行 + 存儲過程封裝業務邏輯**。掌握 group_concat_max_len、列名轉義、條件流程控制、參數安全、資源釋放五大關鍵點，可構建通用、高性能、安全的動態透視查詢服務。