SQL 变换结果集成一行

SQL 变换结果集成一行,你想把若干行数据重新组合成一行新数据,原始数据集的每一行变换后会作为新数据的一列出现。例如,下面的结果集展示了每个部门的员工人数。

SQL 变换结果集成一行 问题描述

你想把若干行数据重新组合成一行新数据,原始数据集的每一行变换后会作为新数据的一列出现。例如,下面的结果集展示了每个部门的员工人数。

DEPTNO        CNT
------ ----------
    10          3
    20          5
    30          6

你希望把上述结果集重新格式化成下面的输出结果。

DEPTNO_10  DEPTNO_20  DEPTNO_30
--------- ---------- ----------
        3          5          6

SQL 变换结果集成一行 解决方案

使用 CASE 表达式和聚合函数 SUM 实现结果集变换。

1 select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
2        sum(case when deptno=20 then 1 else 0 end) as deptno_20,
3        sum(case when deptno=30 then 1 else 0 end) as deptno_30
4 from emp

SQL 变换结果集成一行 扩展知识

这是一个非常好的结果集变换入门实例。它的做法其实很简单:对于每一行的原始数据,使用 CASE 表达式把行变换成列。然后,由于本实例要合计每个部门的员工人数,因此需要调用聚合函数 SUM 计算出每个 DEPTNO 出现的次数。如果你还是不太理解以上解决方案的工作原理,那么不妨先执行下面的查询,该查询调用聚合函数 SUM,并加上了 DEPTNO 以增强可读性。

select deptno,
       case when deptno=10 then 1 else 0 end as deptno_10,
       case when deptno=20 then 1 else 0 end as deptno_20,
       case when deptno=30 then 1 else 0 end as deptno_30
  from emp
 order by 1
 
DEPTNO  DEPTNO_10  DEPTNO_20  DEPTNO_30
------ ---------- ---------- ----------
    10          1          0          0
    10          1          0          0
    10          1          0          0
    20          0          1          0
    20          0          1          0
    20          0          1          0
    20          0          1          0
    20          0          1          0
    30          0          0          1
    30          0          0          1
    30          0          0          1
    30          0          0          1
    30          0          0          1
    30          0          0          1

我们可以把每一个 CASE 表达式的结果值想象成一个标志位,它表示每个 DEPTNO 属于哪一行。至此,“把行变成列”的操作已经完成。下一步只要合计 DEPTNO_10DEPTNO_20DEPTNO_30 的值,并按照 DEPTNO 分组即可,结果集显示如下。

select deptno,
       sum(case when deptno=10 then 1 else 0 end) as deptno_10,
       sum(case when deptno=20 then 1 else 0 end) as deptno_20,
       sum(case when deptno=30 then 1 else 0 end) as deptno_30
  from emp
 group by deptno
 
DEPTNO  DEPTNO_10  DEPTNO_20  DEPTNO_30
------ ---------- ---------- ----------
    10          3          0          0
    20          0          5          0
    30          0          0          6

仔细观察上述结果集的话,可以发现上述输出结果在逻辑上是正确的。例如,DEPTNO 等于 10 的部门对应的行里面,DEPTNO_10 的值为 3,而其他列都是 0。因为我们的目标是只返回一行数据,最后一步就是要舍弃 DEPTNOGROUP BY,只针对 CASE 表达式的结果执行 SUM 函数即可。

select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
       sum(case when deptno=20 then 1 else 0 end) as deptno_20,
       sum(case when deptno=30 then 1 else 0 end) as deptno_30
  from emp
 
DEPTNO_10  DEPTNO_20  DEPTNO_30
--------- ---------- ----------
        3          5          6

对于这一类问题,还有一种可能的做法。

select max(case when deptno=10 then empcount else null end) as deptno_10,
       max(case when deptno=20 then empcount else null end) as deptno_20,
       max(case when deptno=30 then empcount else null end) as deptno_30
  from (
select deptno, count(*) as empcount
  from emp
 group by deptno
       ) x

上述方法用内嵌视图生成每个部门的员工总人数。主查询里的 CASE 表达式把行转换成列,将得到下面的结果。

DEPTNO_10  DEPTNO_20  DEPTNO_30
--------- ---------- ----------
        3       Null       Null
     Null          5       Null
     Null       Null          6

然后,调用 MAX 函数把几列合并为一行。

DEPTNO_10  DEPTNO_20  DEPTNO_30
--------- ---------- ----------
        3          5          6

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程