MySQL 行转列详情-木庄网络博客

本文整理自网络，侵删。

MySQL行转列，对经常处理数据的同学们来说，一定是不陌生的，甚至是印象深刻，因为它大概率困扰过你，让你为之一愣~ 但当你看到本文后，这个问题就不在是问题，及时收藏，以后谁再问你这个问题，直接甩他脸上，粘贴即用。

首先，我们看一下咱们的测试表数据和预期查询的结果：

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进行行转列

ELECT 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()进行行转列：

ELECT 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)