5分钟搞懂MySQL - 行转列_mysql 性别分完组后,行转列

好，下面我们一起来看看SQL是如何编写的，对了，创建表结构和导入测试数据的SQL放到文章末尾了，自取~

---

飞机票

• 一、行转列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()。

• 方法二、使用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;

查询结果：

+--------------+--------+--------+--------+--------------+--------+
| 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;

我们来看一下输出结果：

# 面试题总结

**其它面试题（springboot、mybatis、并发、java中高级面试总结等）**

![](https://img-blog.csdnimg.cn/img_convert/0edcc5c9c92a085ccdec3c3191cab630.webp?x-oss-process=image/format,png)

![](https://img-blog.csdnimg.cn/img_convert/5f65d075b743735d809e7f66064c9145.webp?x-oss-process=image/format,png)

![](https://img-blog.csdnimg.cn/img_convert/783438163d5eb330d9580b8cb229a489.webp?x-oss-process=image/format,png)


我们来看一下输出结果：

面试题总结

其它面试题（springboot、mybatis、并发、java中高级面试总结等）

[外链图片转存中…(img-wDDzgB2a-1725866077331)]

[外链图片转存中…(img-NHqPKu8m-1725866077332)]

[外链图片转存中…(img-SZNwtswP-1725866077332)]