当前位置：首页 > news >正文

MySQL 行转列与列转行的实现方式

news 2025/8/26 5:58:26

在 MySQL 中，行转列（Pivot）和列转行（Unpivot）是常见的数据转换需求。以下是这两种操作的实现方式：

行转列（Pivot）

行转列是将多行数据转换为一行多列的操作。

1. 使用 CASE WHEN 和聚合函数

这是最常用的行转列方法，适用于已知转换后列名的情况。

-- 示例数据
CREATE TABLE sales (year INT,quarter INT,amount DECIMAL(10,2)
);INSERT INTO sales VALUES
(2020, 1, 1000),
(2020, 2, 1500),
(2020, 3, 2000),
(2020, 4, 1800),
(2021, 1, 1200),
(2021, 2, 1600),
(2021, 3, 2200),
(2021, 4, 1900);-- 行转列：将季度数据转换为列
SELECT year,SUM(CASE WHEN quarter = 1 THEN amount ELSE 0 END) AS Q1,SUM(CASE WHEN quarter = 2 THEN amount ELSE 0 END) AS Q2,SUM(CASE WHEN quarter = 3 THEN amount ELSE 0 END) AS Q3,SUM(CASE WHEN quarter = 4 THEN amount ELSE 0 END) AS Q4,SUM(amount) AS Total
FROM sales
GROUP BY year;

2. 使用 IF 函数

与 CASE WHEN 类似，但语法更简洁：

SELECT year,SUM(IF(quarter = 1, amount, 0)) AS Q1,SUM(IF(quarter = 2, amount, 0)) AS Q2,SUM(IF(quarter = 3, amount, 0)) AS Q3,SUM(IF(quarter = 4, amount, 0)) AS Q4,SUM(amount) AS Total
FROM sales
GROUP BY year;

3. 动态行转列（当列名不确定时）

当需要转换的列值不固定时，可以使用存储过程动态生成 SQL：

DELIMITER //CREATE PROCEDURE dynamic_pivot()
BEGINSET @sql = NULL;-- 获取所有不同的季度值SELECT GROUP_CONCAT(DISTINCTCONCAT('SUM(IF(quarter = ', quarter, ', amount, 0)) AS Q', quarter)) INTO @sqlFROM sales;-- 构建完整查询SET @sql = CONCAT('SELECT year, ', @sql, ', SUM(amount) AS Total FROM sales GROUP BY year');-- 准备并执行语句PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
END //DELIMITER ;-- 调用存储过程
CALL dynamic_pivot();

列转行（Unpivot）

列转行是将一行多列数据转换为多行数据的操作。

1. 使用 UNION ALL

这是最常用的列转行方法：

-- 示例数据（行转列后的结果）
CREATE TABLE sales_pivoted (year INT,Q1 DECIMAL(10,2),Q2 DECIMAL(10,2),Q3 DECIMAL(10,2),Q4 DECIMAL(10,2),Total DECIMAL(10,2)
);INSERT INTO sales_pivoted VALUES
(2020, 1000, 1500, 2000, 1800, 6300),
(2021, 1200, 1600, 2200, 1900, 6900);-- 列转行：将季度列转换为行
SELECT year, 1 AS quarter, Q1 AS amount FROM sales_pivoted
UNION ALL
SELECT year, 2 AS quarter, Q2 AS amount FROM sales_pivoted
UNION ALL
SELECT year, 3 AS quarter, Q3 AS amount FROM sales_pivoted
UNION ALL
SELECT year, 4 AS quarter, Q4 AS amount FROM sales_pivoted
ORDER BY year, quarter;

2. 使用 CROSS JOIN 和 VALUES（MySQL 8.0+）

MySQL 8.0 引入了 VALUES 语句，可以简化列转行操作：

-- 使用 VALUES 和 CROSS JOIN
SELECT s.year,v.quarter,CASE v.quarterWHEN 1 THEN s.Q1WHEN 2 THEN s.Q2WHEN 3 THEN s.Q3WHEN 4 THEN s.Q4END AS amount
FROM sales_pivoted s
CROSS JOIN (VALUES ROW(1),ROW(2),ROW(3),ROW(4)
) AS v(quarter)
ORDER BY s.year, v.quarter;

使用 JSON 函数进行行列转换（MySQL 5.7+）

MySQL 5.7 引入了 JSON 函数，可以用于更灵活的行列转换：

行转列使用 JSON

SELECT year,JSON_OBJECT('Q1', SUM(IF(quarter = 1, amount, 0)),'Q2', SUM(IF(quarter = 2, amount, 0)),'Q3', SUM(IF(quarter = 3, amount, 0)),'Q4', SUM(IF(quarter = 4, amount, 0))) AS quarterly_data
FROM sales
GROUP BY year;

列转行使用 JSON

SELECTyear,js.quarter,js.amount
FROM sales_pivoted,
JSON_TABLE(JSON_OBJECT('Q1', Q1,'Q2', Q2,'Q3', Q3,'Q4', Q4),'$.*' COLUMNS(quarter VARCHAR(10) PATH '$[0]',amount DECIMAL(10,2) PATH '$[1]')
) AS js;

总结

转换类型	方法	适用场景	优点	缺点
行转列	CASE WHEN/IF + 聚合函数	列名已知且固定	简单直观，性能好	列名固定，不够灵活
行转列	动态SQL	列名不固定	灵活适应数据变化	需要编写存储过程，较复杂
列转行	UNION ALL	任何MySQL版本	简单通用	代码冗长，性能随列数增加而下降
列转行	CROSS JOIN + VALUES	MySQL 8.0+	代码简洁	需要MySQL 8.0+
行列转换	JSON函数	MySQL 5.7+	灵活处理复杂结构	语法复杂，性能可能较差