小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL专栏目录 | 点击这里
MySQL行转列,对经常处理数据的同学们来说,一定是不陌生的,甚至是印象深刻,因为它大概率困扰过你,让你为之一愣~ 但当你看到本文后,这个问题就不在是问题,及时收藏,以后谁再问你这个问题,直接甩他脸上,粘贴即用。
这里告诉大家一个小秘密,其实我和《小欢喜》里面的几位是同学还是好朋友~~今天我就拿我们哥儿几个高考成绩表来当测试表。嗯,兄弟们不信?我还有我们在学校里的合影呢,高三那年还没有PS,所以照片肯定是真的!
废话不多说,首先,我们看一下咱们的测试表数据和预期查询的结果。 - 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)
复制代码
看看我们行转列转完后的结果: - +--------------+--------+--------+--------+--------------+
- | student_name | 语文 | 数学 | 英语 | 特长加分 |
- +--------------+--------+--------+--------+--------------+
- | 林磊儿 | 148 | 150 | 147 | 0 |
- | 乔英子 | 121 | 106 | 146 | 0 |
- | 方一凡 | 70 | 90 | 59 | 200 |
- | 陈哈哈 | 109 | 92 | 80 | 0 |
- +--------------+--------+--------+--------+--------------+
- 4 rows in set (0.00 sec)
复制代码
好,下面我们一起来看看SQL是如何编写的,对了,创建表结构和导入测试数据的SQL放到文章末尾了,自取~
一、行转列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()。
- 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;
复制代码
查询结果: - +--------------+--------+--------+--------+--------------+--------+
- | student_name | 语文 | 数学 | 英语 | 特长加分 | 总数 |
- +--------------+--------+--------+--------+--------------+--------+
- | 乔英子 | 121 | 106 | 146 | 0 | 373 |
- | 方一凡 | 70 | 90 | 59 | 200 | 419 |
- | 林磊儿 | 148 | 150 | 147 | 0 | 445 |
- | 陈哈哈 | 113 | 116 | 80 | 0 | 309 |
- | 总数 | 452 | 462 | 432 | 200 | 1546 |
- +--------------+--------+--------+--------+--------------+--------+
- 5 rows in set, 1 warning (0.00 sec)
复制代码
三、领导又双叒叕@你改需求
让你把分值转化为具体内容显示(优秀、良好、普通、差),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;
复制代码
我们来看一下输出结果: - +--------------+--------+--------+--------+--------+--------------+
- | student_name | 语文 | 数学 | 英语 | 总分 | 结果 |
- +--------------+--------+--------+--------+--------+--------------+
- | 林磊儿 | 优秀 | 优秀 | 优秀 | 445 | 重点大学 |
- | 方一凡 | 差 | 差 | 差 | 419 | 一本 |
- | 乔英子 | 普通 | 差 | 优秀 | 373 | 二本 |
- | 陈哈哈 | 普通 | 普通 | 差 | 309 | 工地搬砖 |
- +--------------+--------+--------+--------+--------+--------------+
- 4 rows in set (0.00 sec)
复制代码
过来人的经验来看,老实孩子最吃亏,早知道他娘的走艺体了~
四、结束语
好了,SQL方面就是以上这些内容了,有疑问可以写在评论区,哈哥会在摸鱼的时候回复你~~`
帮忙三连一下哦,比心( ` )
附录:创建表结构&测试数据SQL
表结构: - 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 BTEE
- ) 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);
复制代码 来源:https://blog.csdn.net/qq_39390545/article/details/122401862 免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作! |