SQL 反向变换结果集|极客教程

SQL 反向变换结果集，你想把列数据变换成行数据，考虑如下所示的结果集。仔细查看上述的最终结果集，很容易想到我们只需针对 EMP 表执行 COUNT 和 GROUP BY 查询，就可以得到符合要求的结果集。

SQL 反向变换结果集问题描述

你想把列数据变换成行数据，考虑如下所示的结果集。

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

你希望转换为：

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

SQL 反向变换结果集解决方案

仔细查看上述的最终结果集，很容易想到我们只需针对 EMP 表执行 COUNT 和 GROUP BY 查询，就可以得到符合要求的结果集。然后，对于本实例而言，要先假设原始数据集没有被按照行形式存储，我们可以认为数据是存在于几个列中的。
为了把列数据变换成行数据，需要用到笛卡儿积。我们事先要知道有多少列需要被转换为行形式，因为创建笛卡儿积时用到的表表达式（table expression）必须有一个基数（cardinality），该基数至少要等于需要做变换的列的个数。
在这里，我们不必去创建一个“去规范化表”（denormalized table），只需重新使用 12.1 节中实例的代码生成一个“宽”结果集即可。完整的解决方案如下所示。

 1  select dept.deptno,
 2         case dept.deptno
 3              when 10 then emp_cnts.deptno_10
 4              when 20 then emp_cnts.deptno_20
 5              when 30 then emp_cnts.deptno_30
 6         end as counts_by_dept
 7    from (
 8  select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
 9         sum(case when deptno=20 then 1 else 0 end) as deptno_20,
10         sum(case when deptno=30 then 1 else 0 end) as deptno_30
11    from emp
12         ) emp_cnts,
13         (select deptno from dept where deptno <= 30) dept

SQL 反向变换结果集扩展知识

内嵌视图 EMP_CNTS 就是上述的非规范化视图，即“宽”结果集，也就是变换前的列形式的数据，如下所示。

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

因为上述数据分为 3 列存储，所以我们需要生成 3 行新数据。首先基于内嵌视图 EMP_CNTS 和一个至少有 3 行数据的表构造一个笛卡儿积。下面的代码借助 DEPT 表构造了一个笛卡儿积，DEPT 表里有 3 行数据。

select dept.deptno,
       emp_cnts.deptno_10,
       emp_cnts.deptno_20,
       emp_cnts.deptno_30
  from (
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
       ) emp_cnts,
       (select deptno from dept where deptno <= 30) dept
　
DEPTNO  DEPTNO_10  DEPTNO_20  DEPTNO_30
------ ---------- ---------- ----------
    10          3          5          6
    20          3          5          6
    30          3          5          6

笛卡儿积使得我们能够为内嵌视图 EMP_CNTS 的每一列返回一行数据。由于最终的结果集需要 DEPTNO 和该 DEPTNO 对应的员工人数，因此要用 CASE 表达式把每行 3 列变成每行 1 列。

select dept.deptno,
       case dept.deptno
            when 10 then emp_cnts.deptno_10
            when 20 then emp_cnts.deptno_20
            when 30 then emp_cnts.deptno_30
       end as counts_by_dept
  from (
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
       ) emp_cnts,
       (select deptno from dept where deptno <= 30) dept
　
DEPTNO COUNTS_BY_DEPT
------ --------------
    10              3
    20              5
    30              6