MySQL数据库行转列，列转行_mysql行转列sql

一，MySQL行转列

导入数据表数据

CREATE TABLE `tb_student_grade`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `course` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `score` float NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
)
INSERT INTO `tb_student_grade` VALUES (1, '张三', '语文', 85);
INSERT INTO `tb_student_grade` VALUES (2, '张三', '数学', 92);
INSERT INTO `tb_student_grade` VALUES (3, '张三', '英语', 87);
INSERT INTO `tb_student_grade` VALUES (4, '李四', '语文', 96);
INSERT INTO `tb_student_grade` VALUES (5, '李四', '数学', 89);
INSERT INTO `tb_student_grade` VALUES (6, '李四', '英语', 100);
INSERT INTO `tb_student_grade` VALUES (7, '王五', '语文', 91);
INSERT INTO `tb_student_grade` VALUES (8, '王五', '数学', 83);
INSERT INTO `tb_student_grade` VALUES (9, '王五', '英语', 98);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

导入之后查询数据列表

对数据进行 行转列 操作，效果图：

行转列 sql语句

SELECT
	s_name AS '姓名',
	MAX( CASE course WHEN '语文' THEN score ELSE '' END ) AS '语文',
	MAX( CASE course WHEN '数学' THEN score ELSE '' END ) AS '数学',
	MAX( CASE course WHEN '英语' THEN score ELSE '' END ) AS '英语' 
FROM
	tb_student_grade 
GROUP BY
	s_name
1
2
3
4
5
6
7
8
9

二，MySQL 列转行

导入数据表数据

CREATE TABLE `tb_student_grade2`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `yuwen` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `shuxue` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `yingyu` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
)
INSERT INTO `tb_student_grade2` VALUES (1, '张三', '85', '92', '87');
INSERT INTO `tb_student_grade2` VALUES (2, '李四', '96', '89', '100');
INSERT INTO `tb_student_grade2` VALUES (3, '王五', '91', '83', '98');

1
2
3
4
5
6
7
8
9
10
11
12

导入后查询数据列表结果集

对数据进行 列转行 操作，效果图

列转行 sql语句

SELECT s_name,'语文' as course,yuwen as score FROM tb_student_grade2 UNION
SELECT s_name,'数学' as course,shuxue as score FROM tb_student_grade2 UNION
SELECT s_name,'英语' as course,yingyu as score FROM tb_student_grade2;
1
2
3

按名字排序，效果图：

sql数据

select a.s_name,a.course,a.score 
from(SELECT s_name,'语文' as course,yuwen as score FROM tb_student_grade2 UNION
SELECT s_name,'数学' as course,shuxue as score FROM tb_student_grade2 UNION
SELECT s_name,'英语' as course,yingyu as score FROM tb_student_grade2) a
order by a.s_name;
1
2
3
4
5

完毕 ！！