SQL中如何将行转成列详解

吾爱主题阅读：173 2022-12-01 16:42:00 评论：0

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

SQL中怎么将行转成列？

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

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

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

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

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

1 2 3 4 5 6 7 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() 函数实现行转列，参考如下代码：

1 2 3 4 5 6 7 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实现如下：

1 2 3 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。