5分钟搞懂MySQL - 行转列
【摘要】
首先，创建表结构和导入测试数据的SQL
#创建表结构DROP TABLE IF EXISTS `t_gaokao_score`;CREATE TABLE `t_gaokao_score` ( `id` int(0)&...
-
#创建表结构
-
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;
该方法将IF(subject='语文',score,0)作为条件，通过student_name进行分组，对分组后所有subject='语文’的记录的score字段进行SUM()操作，如果score没有值则默认为0。这种方式和case..when..then方法原理相同，相比更加简洁明了，建议使用。
二、如果领导@你，让你在结果集中加上总数列呢？
友情提示：我们工作中处理行转列数据时，尽量都把总数、平均数等加上，方便领导查阅，省得他循环BB你。
写法：利用SUM(IF()) 生成列，WITH ROLLUP 生成汇总列和行,并利用 IFNULL将汇总行标题显示为总数
-
#来源公众号【码农编程进阶笔记】
-
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方面就是以上这些内容了，有疑问可以写在评论区留言！
文章来源: blog.csdn.net，作者：lxw1844912514，版权归原作者所有，如需转载，请联系作者。
原文链接：blog.csdn.net/lxw1844912514/article/details/125383333
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）