您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 行轉列實戰：利用 SUM(IF()) 與 WITH ROLLUP 實現動態透視表

編輯：MySQL 資料庫相關知識

一、業務場景

原始表 test(year, month, amount) 存儲按年月的金額流水，需轉換為「年份為行、月份為列」的透視報表：

年份 | 1月 | 2月 | 3月 | 4月
1991 | 1.1 | 1.2 | 1.3 | 1.4
1992 | 2.1 | 2.2 | 2.3 | 2.3

二、核心技巧：條件聚合

SELECT
    year,
    SUM(IF(month = 1, amount, 0)) AS M1,
    SUM(IF(month = 2, amount, 0)) AS M2,
    SUM(IF(month = 3, amount, 0)) AS M3,
    SUM(IF(month = 4, amount, 0)) AS M4
FROM test
GROUP BY year;

原理：IF(condition, true_val, false_val) 將非目標月份金額置 0，SUM 聚合即得到單月總額。GROUP BY year 保證每年一行。

三、進階：自動生成匯總行 WITH ROLLUP

SELECT
    IFNULL(year, 'Total') AS year,
    SUM(IF(month = 1, amount, 0)) AS M1,
    SUM(IF(month = 2, amount, 0)) AS M2,
    SUM(IF(month = 3, amount, 0)) AS M3,
    SUM(IF(month = 4, amount, 0)) AS M4
FROM test
GROUP BY year WITH ROLLUP;

輸出最後一行 year='Total' 為全年彙總，IFNULL 美化顯示。

四、動態月份列（預處理語句）

若月份不固定，可構建動態 SQL：

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('SUM(IF(month = ', month, ', amount, 0)) AS `M', month, '`')
) INTO @sql FROM test;

SET @sql = CONCAT('SELECT year, ', @sql, ' FROM test GROUP BY year WITH ROLLUP');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

五、性能與替代方案對比

方案	優點	缺點	適用場景
條件聚合 SUM(IF)	標準 SQL、無版本依賴、優化器友好	列固定需手寫/動態 SQL	中小數據集、列數可控
MySQL 8.0 CTE + 聚合	可讀性強、支援遞歸	需 8.0+	現代版本、複雜邏輯
應用層轉換	SQL 簡單、靈活	傳輸冗餘數據、佔用內存	結果集小、前端渲染
存儲過程	封裝邏輯、復用	調試難、業務邏輯下沉	固定報表、定時任務