一，数据库的行转列
导入数据库数据
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);
数据库数据长这个样子
行转列之后长这个样子
sql这样写
SELECT s_name,
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
二,数据库的列转行
导入数据库数据
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');
数据库长这样子
列转行之后长这个样子
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;
如果想要按名字排序可以这样子写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;
