5分钟搞懂MySQL - 行转列
首先，创建表结构和导入测试数据的SQL
#创建表结构
DROP TABLE IF EXISTS `t_gaokao_score`;
CREATE TABLE `t_gaokao_score` (
`id` int() NOT NULL AUTO_INCREMENT,
`student_name` varchar() CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
`subject` varchar() 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 = CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;#导入测试数据
然后，我们看一下咱们的测试表数据和预期查询的结果。
#来源公众号【码农编程进阶笔记】
mysql> SELECT * FROM t_gaokao_score;
+----+--------------+--------------+-------+
| id | student_name | subject | score |
+----+--------------+--------------+-------+
| | 林磊儿 | 语文 | 148 |
| 2 | 林磊儿 | 数学 | |
| | 林磊儿 | 英语 | 147 |
| 4 | 乔英子 | 语文 | |
| | 乔英子 | 数学 | 106 |
| 6 | 乔英子 | 英语 | |
| | 方一凡 | 语文 | 70 |
| 8 | 方一凡 | 数学 | |
| | 方一凡 | 英语 | 59 |
| 10 | 方一凡 | 特长加分 | |
| | 陈哈哈 | 语文 | 109 |
| 12 | 陈哈哈 | 数学 | |
| | 陈哈哈 | 英语 | 80 |
+----+--------------+--------------+-------+
rows in set (. sec)
看看我们行转列转完后的结果:
一、行转列SQL写法
方法一：使用case..when..then进行 行转列
#来源公众号【码农编程进阶笔记】
SELECT student_name,
SUM(CASE `subject` WHEN '语文' THEN score ELSE END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE END) as '英语',
SUM(CASE `subject` WHEN '特长加分' THEN score ELSE 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,)) as '语文',
SUM(IF(`subject`='数学',score,)) as '数学',
SUM(IF(`subject`='英语',score,)) as '英语',
SUM(IF(`subject`='特长加分',score,)) 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,)) AS '语文',
SUM(IF(`subject`='数学',score,)) AS '数学',
SUM(IF(`subject`='英语',score,)) AS '英语',
SUM(IF(`subject`='特长加分',score,)) 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='语文') > THEN
'优秀'
WHEN score - (select avg(score) from t_gaokao_score where subject='语文') > THEN
'良好'
WHEN score - (select avg(score) from t_gaokao_score where subject='语文') >= THEN
'普通'
ELSE
'差'
END
)
END
) as '语文',
MAX(
CASE subject
WHEN '数学' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='数学') > THEN
'优秀'
WHEN score - (select avg(score) from t_gaokao_score where subject='数学') > THEN
'良好'
WHEN score - (select avg(score) from t_gaokao_score where subject='数学') >= THEN
'普通'
ELSE
'差'
END
)
END
) as '数学',
MAX(
CASE subject
WHEN '英语' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='英语') > THEN
'优秀'
WHEN score - (select avg(score) from t_gaokao_score where subject='英语') > THEN
'良好'
WHEN score - (select avg(score) from t_gaokao_score where subject='英语') >= THEN
'普通'
ELSE
'差'
END
)
END
) as '英语',
SUM(score) as '总分',
(CASE WHEN SUM(score) > THEN '重点大学'
WHEN SUM(score) > THEN '一本'
WHEN SUM(score) > THEN '二本'
ELSE '工地搬砖'
END ) as '结果'
FROM t_gaokao_score
GROUP BY student_name
ORDER BY SUM(score) desc;我们来看一下输出结果：
好了，SQL方面就是以上这些内容了，有疑问可以写在评论区留言！
腾讯云开发者
