5分钟搞懂MySQL - 行转列
码农编程进阶笔记
共 13076字，需浏览 27分钟
·
2022-06-21 12:47
#创建表结构
DROP TABLE IF EXISTS `t_gaokao_score`;
CREATE TABLE `t_gaokao_score` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`student_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
`subject` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目',
`score` double NULL DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;#导入测试数据INSERT INTO `t_gaokao_score` VALUES
(1, '林磊儿', '语文', 148),
(2, '林磊儿', '数学', 150),
(3, '林磊儿', '英语', 147),
(4, '乔英子', '语文', 121),
(5, '乔英子', '数学', 106),
(6, '乔英子', '英语', 146),
(7, '方一凡', '语文', 70),
(8, '方一凡', '数学', 90),
(9, '方一凡', '英语', 59),
(10, '方一凡', '特长加分', 200),
(11, '陈哈哈', '语文', 109),
(12, '陈哈哈', '数学', 92),
(13, '陈哈哈', '英语', 80);#来源公众号【码农编程进阶笔记】
mysql> SELECT * FROM t_gaokao_score;
+----+--------------+--------------+-------+
| id | student_name | subject | score |
+----+--------------+--------------+-------+
| 1 | 林磊儿 | 语文 | 148 |
| 2 | 林磊儿 | 数学 | 150 |
| 3 | 林磊儿 | 英语 | 147 |
| 4 | 乔英子 | 语文 | 121 |
| 5 | 乔英子 | 数学 | 106 |
| 6 | 乔英子 | 英语 | 146 |
| 7 | 方一凡 | 语文 | 70 |
| 8 | 方一凡 | 数学 | 90 |
| 9 | 方一凡 | 英语 | 59 |
| 10 | 方一凡 | 特长加分 | 200 |
| 11 | 陈哈哈 | 语文 | 109 |
| 12 | 陈哈哈 | 数学 | 92 |
| 13 | 陈哈哈 | 英语 | 80 |
+----+--------------+--------------+-------+
13 rows in set (0.00 sec)
一、行转列SQL写法
方法一：使用case..when..then进行 行转列
#来源公众号【码农编程进阶笔记】
SELECT student_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 '英语',
SUM(CASE `subject` WHEN '特长加分' THEN score ELSE 0 END) as '特长加分'
FROM t_gaokao_score
GROUP BY student_name;这里如果不使用SUM()会报sql_mode=only_full_group_by相关错误，需要聚合函数和group by连用或使用distinct才可以解决。
其实，加了SUM()是为了能够使用GROUP BY根据student_name进行分组，每一个student_name对应的subject="语文"的记录毕竟只有一条，所以SUM() 的值就等于对应那一条记录的score的值。当然，也可以换成MAX()。
方法二：使用IF()进行 行转列：
#来源公众号【码农编程进阶笔记】
SELECT student_name,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='特长加分',score,0)) as '特长加分'
FROM t_gaokao_score
GROUP BY student_name;二、如果领导@你，让你在结果集中加上总数列呢？
#来源公众号【码农编程进阶笔记】
SELECT IFNULL(student_name,'总数') AS student_name,
SUM(IF(`subject`='语文',score,0)) AS '语文',
SUM(IF(`subject`='数学',score,0)) AS '数学',
SUM(IF(`subject`='英语',score,0)) AS '英语',
SUM(IF(`subject`='特长加分',score,0)) AS '特长加分',
SUM(score) AS '总数'
FROM t_gaokao_score
GROUP BY student_name WITH ROLLUP;
三、领导又双叒叕@你改需求
让你把分值转化为具体内容显示（优秀、良好、普通、差），430分以上重点大学，400分以上一本，350分及以上二本，350以下搬砖，该怎么写呢？
这里我们就需要case when嵌套一下了，看着高大上，其实就是普通的嵌套而已。在第一层查出分组后的各科分数，在第二层替换成等级即可。
SELECT student_name,
MAX(
CASE subject
WHEN '语文' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='语文') > 20 THEN
'优秀'
WHEN score - (select avg(score) from t_gaokao_score where subject='语文') > 10 THEN
'良好'
WHEN score - (select avg(score) from t_gaokao_score where subject='语文') >= 0 THEN
'普通'
ELSE
'差'
END
)
END
) as '语文',
MAX(
CASE subject
WHEN '数学' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='数学') > 20 THEN
'优秀'
WHEN score - (select avg(score) from t_gaokao_score where subject='数学') > 10 THEN
'良好'
WHEN score - (select avg(score) from t_gaokao_score where subject='数学') >= 0 THEN
'普通'
ELSE
'差'
END
)
END
) as '数学',
MAX(
CASE subject
WHEN '英语' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='英语') > 20 THEN
'优秀'
WHEN score - (select avg(score) from t_gaokao_score where subject='英语') > 10 THEN
'良好'
WHEN score - (select avg(score) from t_gaokao_score where subject='英语') >= 0 THEN
'普通'
ELSE
'差'
END
)
END
) as '英语',
SUM(score) as '总分',
(CASE WHEN SUM(score) > 430 THEN '重点大学'
WHEN SUM(score) > 400 THEN '一本'
WHEN SUM(score) > 350 THEN '二本'
ELSE '工地搬砖'
END ) as '结果'
FROM t_gaokao_score
GROUP BY student_name
ORDER BY SUM(score) desc;我们来看一下输出结果：
好了，SQL方面就是以上这些内容了，有疑问可以写在评论区留言！
评论