目录
MySQL行列转换详解
行列转换是SQL中常见的操作，主要包括两种类型：行转列（PIVOT）和列转行（UNPIVOT）。MySQL虽然没有直接的PIVOT/UNPIVOT语法，但可以通过条件聚合和UNION ALL等方式实现。
一、行转列（PIVOT）
行转列是将行数据转换为列数据，通常用于将分类数据作为列名展示。
示例场景：学生成绩表行转列
1. 创建原始表
CREATE TABLE student_scores (
student_id INT,
student_name VARCHAR(50),
subject VARCHAR(50),
score INT
);
INSERT INTO student_scores VALUES
(1, '张三', '数学', 90),
(1, '张三', '语文', 85),
(1, '张三', '英语', 92),
(2, '李四', '数学', 88),
(2, '李四', '语文', 90),
(2, '李四', '英语', 87),
(3, '王五', '数学', 95),
(3, '王五', '语文', 78),
(3, '王五', '英语', 85);2. 行转列实现方法
使用条件聚合函数：
SELECT
student_id,
student_name,
MAX(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS '数学',
MAX(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS '语文',
MAX(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS '英语'
FROM student_scores
GROUP BY student_id, student_name;结果：
+------------+--------------+--------+--------+--------+
| student_id | student_name | 数学 | 语文 | 英语 |
+------------+--------------+--------+--------+--------+
| 1 | 张三 | 90 | 85 | 92 |
| 2 | 李四 | 88 | 90 | 87 |
| 3 | 王五 | 95 | 78 | 85 |
+------------+--------------+--------+--------+--------+
二、列转行（UNPIVOT）
列转行是将列数据转换为行数据，通常用于将多列数据转换为键值对形式。
示例场景：销售数据列转行
1. 创建原始表
CREATE TABLE sales_data (
id INT,
product_name VARCHAR(50),
q1_sales INT,
q2_sales INT,
q3_sales INT,
q4_sales INT
);
INSERT INTO sales_data VALUES
(1, '产品A', 120, 150, 180, 200),
(2, '产品B', 90, 110, 130, 150),
(3, '产品C', 200, 220, 240, 260);2. 列转行实现方法
使用UNION ALL：
SELECT
id,
product_name,
'Q1' AS quarter,
q1_sales AS sales
FROM sales_data
UNION ALL
SELECT
id,
product_name,
'Q2' AS quarter,
q2_sales AS sales
FROM sales_data
UNION ALL
SELECT
id,
product_name,
'Q3' AS quarter,
q3_sales AS sales
FROM sales_data
UNION ALL
SELECT
id,
product_name,
'Q4' AS quarter,
q4_sales AS sales
FROM sales_data
ORDER BY id, quarter;结果：
+----+--------------+---------+-------+
| id | product_name | quarter | sales |
+----+--------------+---------+-------+
| 1 | 产品A | Q1 | 120 |
| 1 | 产品A | Q2 | 150 |
| 1 | 产品A | Q3 | 180 |
| 1 | 产品A | Q4 | 200 |
| 2 | 产品B | Q1 | 90 |
| 2 | 产品B | Q2 | 110 |
| 2 | 产品B | Q3 | 130 |
| 2 | 产品B | Q4 | 150 |
| 3 | 产品C | Q1 | 200 |
| 3 | 产品C | Q2 | 220 |
| 3 | 产品C | Q3 | 240 |
| 3 | 产品C | Q4 | 260 |
+----+--------------+---------+-------+
三、动态行列转换
当列名不确定或经常变化时，可以使用动态SQL实现行列转换。
-- 创建存储过程实现动态行转列
DELIMITER //
CREATE PROCEDURE dynamic_pivot()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE col_name VARCHAR(50);
DECLARE col_list TEXT DEFAULT '';
DECLARE cur CURSOR FOR
SELECT DISTINCT subject FROM student_scores;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO col_name;
IF done THEN
LEAVE read_loop;
END IF;
SET col_list = CONCAT(col_list,
IF(col_list = '', '', ', '),
'MAX(CASE WHEN subject = ''', col_name, ''' THEN score ELSE NULL END) AS ''', col_name, '''');
END LOOP;
CLOSE cur;
SET @sql = CONCAT('SELECT student_id, student_name, ', col_list, ' FROM student_scores GROUP BY student_id, student_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
-- 调用存储过程
CALL dynamic_pivot();四、性能优化建议
- 对于大型数据集的列转行，考虑使用临时表替代UNION ALL
- 在行转列时，确保GROUP BY子句包含所有非聚合列
- 为常用转换创建视图，提高查询效率
- 在动态SQL中，注意防止SQL注入
五、实际应用场景
- 报表生成：将行数据转换为适合报表展示的列格式
- 数据透视：分析不同维度的数据关系
- ETL过程：数据清洗和转换
- 数据展示：将数据库格式转换为前端需要的格式