SQL中如何将行转成列详解

行列转换在做报表分析时还是经常会遇到的,今天就说一下如何实现行列转换吧,下面这篇文章主要给大家介绍了关于SQL中如何将行转成列的相关资料,需要的朋友可以参考下

目录

• SQL中怎么将行转成列？
• 1. 使用 CASE…WHEN…THEN 语句实现行转列，参考如下代码：
• 2. 使用 IF() 函数实现行转列，参考如下代码：
• 补充：列转行：union
• 总结

天天这需求就神奇！！！！

SQL中怎么将行转成列？

我们以MySQL数据库为例，来说明行转列的实现方式。

首先，假设我们有一张分数表（tb_score），表中的数据如下图：

然后，我们再来看一下转换之后需要得到的结果，如下图：

可以看出，这里行转列是将原来的subject字段的多行内容选出来，作为结果集中的不同列，并根据userid进行分组显示对应的score。通常，我们有两种方式来实现这种转换。

1. 使用 CASE…WHEN…THEN 语句实现行转列，参考如下代码：

SELECT userid, 
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 tb_score 
GROUP BY userid

注意，SUM() 是为了能够使用GROUP BY根据userid进行分组，因为每一个userid对应的
subject="语文"的记录只有一条，所以SUM() 的值就等于对应那一条记录的score的值。假如userid ='001' and subject='语文' 的记录有两条，则此时SUM() 的值将会是这两条记录的和，同理，使用Max()的值将会是这两条记录里面值最大的一个。但是正常情况下，一个user对应一个subject只有一个分数，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。

2. 使用 IF() 函数实现行转列，参考如下代码：

SELECT userid, 
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 tb_score 
GROUP BY userid

注意， IF(subject='语文',score,0) 作为条件，即对所有subject='语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作，如果score没有值则默认为0。

补充：列转行：union

列转行是上述过程的逆过程，所以其思路也比较直观：

• 行记录由一行变为多行，列字段由多列变为单列；
• 一行变多行需要复制，列字段由多列变单列相当于是堆积的过程，其实也可以看做是复制；
• 一行变多行，那么复制的最直观实现当然是使用union，即分别针对每门课程提取一张衍生表，最后将所有课程的衍生表union到一起即可，其中需要注意字段的对齐

按照这一思路，给出SQL实现如下：

SELECT uid,      
		sum(if(course='语文', score, NULL)) as `语文`,      				sum(if(course='数学', score, NULL)) as `数学`,      				sum(if(course='英语', score, NULL)) as `英语`,      				sum(if(course='物理', score, NULL)) as `物理`,      
		sum(if(course='化学', score, NULL)) as `化学`FROM scoreLongGROUP BY uid

查询结果当然是预期的长表。这里重点解释其中的三个细节：

在每个单门课的衍生表中，例如这句：SELECT uid, ‘语文’ as course, 语文 as score，用单引号包裹起来的课程名称是字符串常量，比如语文课的衍生表中的课程名都叫语文，然后将该列命名为course；第二个用反引号包裹起来的课程名实际上是从宽表中引用这一列的取值，然后将其命名为score。

这实际上对应的一个知识点是：在SQL中字符串的引用用单引号（其实双引号也可以），而列字段名称的引用则是用反引号.

上述用到了where条件过滤成绩为空值的记录，这实际是由于在原表中存在有空值的情况，如不加以过滤则在本例中最终查询记录有10条，其中两条记录的成绩字段为空

最后，本例中用union关键字实现了多表的纵向拼接，实际上用union all更为合理，二者的区别是union会完成记录去重；而union all则简单的拼接，在确定不存在重复或无需去重的情况下其效率更高。

总结

到此这篇关于SQL中如何将行转成列的文章就介绍到这了,更多相关SQL将行转成列内容请搜索北冥有鱼以前的文章或继续浏览下面的相关文章希望大家以后多多支持北冥有鱼！