MySQL列行转换

概述

业务中我们经常会基于MySQL做报表统计, 或者修改了表结构后需要做数据迁移, 本文做一个列行转换的演示

环境准备

创建数据库

create database score;

创建表

DROP TABLE IF EXISTS `score`;
CREATE table `score`(
`id` int PRIMARY KEY auto_increment COMMENT '主键',
`name` VARCHAR(20) COMMENT '姓名',
`subject` VARCHAR(20) COMMENT '科目',
`score` DOUBLE default 0 COMMENT '得分'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT '成绩表';

初始化数据

INSERT INTO `score` VALUES
(1, '张三', '语文', 90),
(2, '张三', '数学', 99),
(3, '张三', '英文', 70),
(4, '李四', '语文', 95),
(5, '李四', '数学', 78),
(6, '李四', '英文', 97);

初始化数据如下:

mysql> select * from score;
+----+--------+---------+-------+
| id | name   | subject | score |
+----+--------+---------+-------+
|  1 | 张三   | 语文    |    90 |
|  2 | 张三   | 数学    |    99 |
|  3 | 张三   | 英文    |    70 |
|  4 | 李四   | 语文    |    95 |
|  5 | 李四   | 数学    |    78 |
|  6 | 李四   | 英文    |    97 |
+----+--------+---------+-------+
6 rows in set (0.00 sec)

列行转换

SELECT 
`id`,
`name`,
SUM(CASE `subject` WHEN '语文' THEN `score` ELSE 0 END) AS '语文',
SUM(CASE `subject` WHEN '数学' THEN `score` ELSE 0 END) AS '数学',
SUM(CASE `subject` WHEN '英文' THEN `score` ELSE 0 END) AS '英文'
FROM `score`
GROUP BY `name`;

列行转换后数据结果如下

+----+--------+--------+--------+--------+
| id | name   | 语文   | 数学   | 英文   |
+----+--------+--------+--------+--------+
|  1 | 张三   |     90 |     99 |     70 |
|  4 | 李四   |     95 |     78 |     97 |
+----+--------+--------+--------+--------+
2 rows in set (0.01 sec)