MySQL 行列轉換（透視表）實戰：SUM+IF+GROUP BY 實現動態列

MySQL 行列轉換（透視表）實戰：SUM+IF+GROUP BY 實現動態列

1. 準備測試數據

CREATE TABLE `gas` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `oil` VARCHAR(100) DEFAULT NULL,
  `district` VARCHAR(100) DEFAULT NULL,
  `price` DOUBLE DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO gas (oil, district, price) VALUES
('#0','寶安',11), ('#0','福田',12), ('#0','羅湖',13),
('#93','寶安',21), ('#93','福田',22), ('#93','羅湖',23),
('#97','寶安',31), ('#97','福田',32), ('#97','羅湖',33);

原始數據（行式）：

2. 行轉列核心 SQL

SELECT 
    IFNULL(oil, 'total') AS oil,
    SUM(IF(district='寶安', price, 0)) AS BA,
    SUM(IF(district='福田', price, 0)) AS FT,
    SUM(IF(district='羅湖', price, 0)) AS LH,
    SUM(price) AS TOTAL
FROM gas
GROUP BY oil WITH ROLLUP;

3. 執行結果（列式透視表）

4. 關鍵點解析

• IF(condition, true_val, false_val)：條件聚合，將行屬性轉為列
• SUM()：聚合計算各分組值
• GROUP BY oil WITH ROLLUP：分組並生成總計行
• IFNULL(oil, 'total')：將 ROLLUP 產生的 NULL 顯示為 'total'

此模式適用於銷售報表、庫存統計、多維度數據分析等場景。