SQL中的行列转置

前言

在实际的开发过程中，我们可能会遇到这样的需求：需要将一行数据按照列拆分为多行，或者是需要将多行数据通过扩展列的形式合并为一行。这在SQL上一般称为行列转置，也就是将行转为列，列转为多行。下面我们一起来看看行列转置是如何实现的。

行列转置

1、行转列

行转列实际上是将多行数据按照某个相同的值通过扩展列的形式合并为一行数据。以下通过一个实例来说明。事先准备好3张表：student5表、score5表、subject5表。

student5表数据如下：

score5表数据如下：

由于表中数据过多，所以这里只这展示了部分数据。

subject5表数据如下：

现在想实现这样的效果：

观察可以发现，这是将多行数据通过扩展列的形式合并为一行的。在SQL中，行转列是通过case … when …then … else … end …语句来实现的。SQL如下：

select
stu.fname 姓名,
case su.fsubname when '英语' then sc.fscore else 0 end 英语,
case su.fsubname when '数学' then sc.fscore else 0 end 数学,
case su.fsubname when '语文' then sc.fscore else 0 end 语文,
case su.fsubname when '历史' then sc.fscore else 0 end 历史
from student5 stu
inner join score5 sc
on stu.fid = sc.fstdid
inner join subject5 su
on su.fsubid = sc.fsubid
order by stu.fname;

这里为了方便查看，用了order by按照姓名排序，看起来更加直观一点，查询结果如下：

通过观察可以发现，只要按照名字将各科目下的分数相加起来就可以得到想要的结果，所以最终的SQL如下：

select
stu.fname 姓名,
sum(case su.fsubname when '英语' then sc.fscore else 0 end) 英语,
sum(case su.fsubname when '数学' then sc.fscore else 0 end) 数学,
sum(case su.fsubname when '语文' then sc.fscore else 0 end) 语文,
sum(case su.fsubname when '历史' then sc.fscore else 0 end) 历史
from student5 stu
inner join score5 sc
on stu.fid = sc.fstdid
inner join subject5 su
on su.fsubid = sc.fsubid
group by stu.fname;

查询结果如下：

sum聚合函数也可以使用max来代替，因为其他的是0，所以这里可以通过选取最大值来实现，所以SQL也可以这样写：

select
stu.fname 姓名,
max(case su.fsubname when '英语' then sc.fscore else 0 end) 英语,
max(case su.fsubname when '数学' then sc.fscore else 0 end) 数学,
max(case su.fsubname when '语文' then sc.fscore else 0 end) 语文,
max(case su.fsubname when '历史' then sc.fscore else 0 end) 历史
from student5 stu
inner join score5 sc
on stu.fid = sc.fstdid
inner join subject5 su
on su.fsubid = sc.fsubid
group by stu.fname;

最后的查询结果是一样的。通过sum或max结合case when语句来是实现最终结果，但这也有一定的限制，那就是必须是数字，否则sum或max聚合函数是不起作用的，而且注意要group by进行分组。

2、列转行

列转行实际上是将列变为行，通过使用union来实现的。现有表stu_grade，表数据如下：

现在想要实现以下效果：

观察可以发现，是将列拆分为为行。SQL如下：

SELECT
	user_name 姓名,
	"语文" as 学科,
	cn_score as 分数
FROM
	stu_grade UNION
SELECT
	user_name 姓名,
	"数学" as 学科,
	math_score as 分数
FROM
	stu_grade UNION
SELECT
	user_name 姓名,
	"英语" as 学科,
	en_score as 分数
FROM
	stu_grade 
ORDER BY
	姓名;

查询结果如下：

一共15条记录。

小结

最后做一个小结：
• 行转列是通过sum | max(case … when … then … else … end) … 语句来实现的。
• 列转行是通过union并集来实现的，按照不同条件将结果分别查询出来，最后将结果用union并起来。

一起学习，一起进步，每天只要进步一点点，时间久了，就是质的飞跃。