5分钟搞懂MySQL - 行转列

小伙伴想精准查找自己想看的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|+----+--------------+--------------+-------+13rowsinset(0.00 sec)

看看我们

行转列

转完后的结果:

+--------------+--------+--------+--------+--------------+| student_name | 语文   | 数学   | 英语   | 特长加分     |+--------------+--------+--------+--------+--------------+| 林磊儿       |148|150|147|0|| 乔英子       |121|106|146|0|| 方一凡       |70|90|59|200|| 陈哈哈       |109|92|80|0|+--------------+--------+--------+--------+--------------+4rowsinset(0.00 sec)

  好，下面我们一起来看看SQL是如何编写的，对了，

创建表结构和导入测试数据的SQL放到文章末尾了

，自取~

飞机票

一、行转列SQL写法

• 方法一、使用case..when..then进行 行转列

SELECT student_name,SUM(CASE`subject`WHEN'语文'THEN score ELSE0END)as'语文',SUM(CASE`subject`WHEN'数学'THEN score ELSE0END)as'数学',SUM(CASE`subject`WHEN'英语'THEN score ELSE0END)as'英语',SUM(CASE`subject`WHEN'特长加分'THEN score ELSE0END)as'特长加分'FROM t_gaokao_score 
GROUPBY 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 
GROUPBY 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
GROUPBY 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|+--------------+--------+--------+--------+--------------+--------+5rowsinset,1 warning (0.00 sec)

三、领导又双叒叕@你改需求

  让你把分值转化为具体内容显示（优秀、良好、普通、差），430分以上重点大学，400分以上一本，350分及以上二本，350以下搬砖，该怎么写呢？

  真恶心，不说了，先淦饭🐶🐶~~（独门绝技：

饭遁

）

  这里我们就需要

case when嵌套

一下了，看着高大上，其实就是普通的嵌套而已。在第一层查出分组后的各科分数，在第二层替换成等级即可。

SELECT student_name,MAX(CASE subject  
        WHEN'语文'THEN(CASEWHEN score -(selectavg(score)from t_gaokao_score where subject='语文')>20THEN'优秀'WHEN score -(selectavg(score)from t_gaokao_score where subject='语文')>10THEN'良好'WHEN score -(selectavg(score)from t_gaokao_score where subject='语文')>=0THEN'普通'ELSE'差'END)END)as'语文',MAX(CASE subject  
        WHEN'数学'THEN(CASEWHEN score -(selectavg(score)from t_gaokao_score where subject='数学')>20THEN'优秀'WHEN score -(selectavg(score)from t_gaokao_score where subject='数学')>10THEN'良好'WHEN score -(selectavg(score)from t_gaokao_score where subject='数学')>=0THEN'普通'ELSE'差'END)END)as'数学',MAX(CASE subject  
        WHEN'英语'THEN(CASEWHEN score -(selectavg(score)from t_gaokao_score where subject='英语')>20THEN'优秀'WHEN score -(selectavg(score)from t_gaokao_score where subject='英语')>10THEN'良好'WHEN score -(selectavg(score)from t_gaokao_score where subject='英语')>=0THEN'普通'ELSE'差'END)END)as'英语',SUM(score)as'总分',(CASEWHENSUM(score)>430THEN'重点大学'WHENSUM(score)>400THEN'一本'WHENSUM(score)>350THEN'二本'ELSE'工地搬砖'END)as'结果'FROM t_gaokao_score 
GROUPBY student_name 
ORDERBYSUM(score)desc;

我们来看一下输出结果：

+--------------+--------+--------+--------+--------+--------------+| student_name | 语文   | 数学   | 英语   | 总分   | 结果         |+--------------+--------+--------+--------+--------+--------------+| 林磊儿       | 优秀   | 优秀   | 优秀   |445| 重点大学     || 方一凡       | 差     | 差     | 差     |419| 一本         || 乔英子       | 普通   | 差     | 优秀   |373| 二本         || 陈哈哈       | 普通   | 普通   | 差     |309| 工地搬砖     |+--------------+--------+--------+--------+--------+--------------+4rowsinset(0.00 sec)

过来人的经验来看，老实孩子最吃亏，早知道他娘的走艺体了~

四、结束语

  好了，SQL方面就是以上这些内容了，有疑问可以写在评论区，哈哥会在摸鱼的时候回复你~~`

  帮忙三连一下哦，比心ღ( ´･ᴗ･` )

附录：创建表结构&测试数据SQL

表结构：

DROPTABLEIFEXISTS`t_gaokao_score`;CREATETABLE`t_gaokao_score`(`id`int(0)NOTNULLAUTO_INCREMENT,`student_name`varchar(20)CHARACTERSET utf8 COLLATE utf8_general_ci NOTNULLCOMMENT'学生姓名',`subject`varchar(20)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULLCOMMENT'科目',`score`doubleNULLDEFAULTNULLCOMMENT'成绩',PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=11CHARACTERSET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;

导入测试数据

INSERTINTO`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);