SQL 为两次变换后的结果集增加列标题|极客教程

SQL 为两次变换后的结果集增加列标题，你想把两个结果集叠加起来，并把它们转换成两列。除此之外，你还希望为每一列中的每一组行数据增加一个列标题。例如，你有两个表，它们分别是公司里从事两个不同领域工作的员工名单（假设是研究领域和应用领域）。

SQL 为两次变换后的结果集增加列标题问题描述

你想把两个结果集叠加起来，并把它们转换成两列。除此之外，你还希望为每一列中的每一组行数据增加一个列标题。例如，你有两个表，它们分别是公司里从事两个不同领域工作的员工名单（假设是研究领域和应用领域）。

select * from it_research
　
DEPTNO ENAME
------ --------------------
   100 HOPKINS
   100 JONES
   100 TONEY
   200 MORALES
   200 P.WHITAKER
   200 MARCIANO
   200 ROBINSON
   300 LACY
   300 WRIGHT
   300 J.TAYLOR
　
select * from it_apps
　
DEPTNO ENAME
------ -----------------
   400 CORRALES
   400 MAYWEATHER
   400 CASTILLO
   400 MARQUEZ
   400 MOSLEY
   500 GATTI
   500 CALZAGHE
   600 LAMOTTA
   600 HAGLER
   600 HEARNS
   600 FRAZIER
   700 GUINN
   700 JUDAH
   700 MARGARITO

你希望创建一个报表，分两列列出每个表的员工。你也希望返回每个 DEPTNO 下的 ENAME。最终你想得到如下所示的结果集。

RESEARCH             APPS
-------------------- ---------------
100                  400
  JONES                MAYWEATHER
  TONEY                CASTILLO
  HOPKINS              MARQUEZ
200                    MOSLEY
  P.WHITAKER           CORRALES
  MARCIANO           500
  ROBINSON             CALZAGHE
  MORALES              GATTI
300                  600
  WRIGHT               HAGLER
  J.TAYLOR             HEARNS
  LACY                 FRAZIER
                       LAMOTTA
                     700
                       JUDAH
                       MARGARITO
                       GUINN

SQL 为两次变换后的结果集增加列标题解决方案

基本上，本解决方案只需要一个简单的 stack-n-pivot 操作（先执行 UNION 操作，然后再做行列翻转）。除此之外，还要做一个额外的操作：DEPTNO 必须要先于 ENAME 被返回。这里使用的技巧是，借助笛卡儿积为每个 DEPTNO 产生一行额外的数据，这样我们就不仅得到所有员工的数据，也得到了 DEPTNO 对应的行。本解决方案采用 Oracle 语法，但是由于 DB2 的窗口函数也支持滑动窗口（Framing 子句），适当修改一下本解决方案不难得到适用于 DB2 的代码。因为 IT_RESEARCH 表和 IT_APPS 表只为本实例而存在，下面的解决方案里也顺便列出了创建这些表的语句。

create table IT_research (deptno number, ename varchar2(20))
　
insert into IT_research values (100,'HOPKINS')
insert into IT_research values (100,'JONES')
insert into IT_research values (100,'TONEY')
insert into IT_research values (200,'MORALES')
insert into IT_research values (200,'P.WHITAKER')
insert into IT_research values (200,'MARCIANO')
insert into IT_research values (200,'ROBINSON')
insert into IT_research values (300,'LACY')
insert into IT_research values (300,'WRIGHT')
insert into IT_research values (300,'J.TAYLOR')
　
create table IT_apps (deptno number, ename varchar2(20))
　
insert into IT_apps values (400,'CORRALES')
insert into IT_apps values (400,'MAYWEATHER')
insert into IT_apps values (400,'CASTILLO')
insert into IT_apps values (400,'MARQUEZ')
insert into IT_apps values (400,'MOSLEY')
insert into IT_apps values (500,'GATTI')
insert into IT_apps values (500,'CALZAGHE')
insert into IT_apps values (600,'LAMOTTA')
insert into IT_apps values (600,'HAGLER')
insert into IT_apps values (600,'HEARNS')
insert into IT_apps values (600,'FRAZIER')
insert into IT_apps values (700,'GUINN')
insert into IT_apps values (700,'JUDAH')
insert into IT_apps values (700,'MARGARITO')
　
 1  select max(decode(flag2,0,it_dept)) research,
 2         max(decode(flag2,1,it_dept)) apps
 3    from (
 4  select sum(flag1)over(partition by flag2
 5                            order by flag1,rownum) flag,
 6         it_dept, flag2
 7    from (
 8  select 1 flag1, 0 flag2,
 9         decode(rn,1,to_char(deptno),'  '||ename) it_dept
10    from (
11  select x.*, y.id,
12         row_number()over(partition by x.deptno order by y.id) rn
13    from (
14  select deptno,
15         ename,
16         count(*)over(partition by deptno) cnt
17    from it_research
18         ) x,
19         (select level id from dual connect by level <= 2) y
20         )
21   where rn <= cnt+1
22  union all
23  select 1 flag1, 1 flag2,
24         decode(rn,1,to_char(deptno),'  '||ename) it_dept
25    from (
26  select x.*, y.id,
27         row_number()over(partition by x.deptno order by y.id) rn
28    from (
29  select deptno,
30         ename,
31         count(*)over(partition by deptno) cnt
32    from it_apps
33         ) x,
34         (select level id from dual connect by level <= 2) y
35         )
36   where rn <= cnt+1
37         ) tmp1
38         ) tmp2
39   group by flag

SQL 为两次变换后的结果集增加列标题扩展知识

和其他的数据仓库和报表类型的查询一样，上述解决方案看起来相当复杂，但是如果拆解开来一一细看的话，不难发现它其实是一个 stack-n-pivot 操作，外加一个笛卡儿积（困难重重且没有任何办法）。拆解上述查询的方法是先仔细查看 UNION ALL 的每个组成部分，然后再把它们合并起来做行列变换。先从 UNION ALL 的后半部分开始。

select 1 flag1, 1 flag2,
       decode(rn,1,to_char(deptno),' '||ename) it_dept
  from (
select x.*, y.id,
       row_number()over(partition by x.deptno order by y.id) rn
  from (
select deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_apps
       ) x,
       (select level id from dual connect by level <= 2) y
       ) z
 where rn <= cnt+1
　
FLAG1      FLAG2 IT_DEPT
----- ---------- --------------------------
    1          1 400
    1          1   MAYWEATHER
    1          1   CASTILLO
    1          1   MARQUEZ
    1          1   MOSLEY
    1          1   CORRALES
    1          1 500
    1          1   CALZAGHE
    1          1   GATTI
    1          1 600
    1          1   HAGLER
    1          1   HEARNS
    1          1   FRAZIER
    1          1   LAMOTTA
    1          1 700
    1          1   JUDAH
    1          1   MARGARITO
    1          1   GUINN

仔细看一下上述结果集究竟是怎么拼凑出来的。把上面的查询分解成最基本的组成部分，即可得到内嵌视图 X，该视图从 IT_APPS 表里取出每个 ENAME 和 DEPTNO，并计算出每个 DEPTNO 对应的员工人数。结果如下所示。

select deptno deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_apps
　
DEPTNO ENAME                       CNT
------ -------------------- ----------
   400 CORRALES                      5
   400 MAYWEATHER                    5
   400 CASTILLO                      5
   400 MARQUEZ                       5
   400 MOSLEY                        5
   500 GATTI                         2
   500 CALZAGHE                      2
   600 LAMOTTA                       4
   600 HAGLER                        4
   600 HEARNS                        4
   600 FRAZIER                       4
   700 GUINN                         3
   700 JUDAH                         3
   700 MARGARITO                     3

下一步是基于内嵌视图 X 返回的行和由 CONNECT BY 从 DUAL 表中产生出来的两行数据创建一个笛卡儿积。该操作的结果集显示如下。

select *
  from (
select deptno deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_apps
       ) x,
       (select level id from dual connect by level <= 2) y
 order by 2
　
DEPTNO ENAME      CNT  ID
------ ---------- --- ---
   500 CALZAGHE     2   1
   500 CALZAGHE     2   2
   400 CASTILLO     5   1
   400 CASTILLO     5   2
   400 CORRALES     5   1
   400 CORRALES     5   2
   600 FRAZIER      4   1
   600 FRAZIER      4   2
   500 GATTI        2   1
   500 GATTI        2   2
   700 GUINN        3   1
   700 GUINN        3   2
   600 HAGLER       4   1
   600 HAGLER       4   2
   600 HEARNS       4   1
   600 HEARNS       4   2
   700 JUDAH        3   1
   700 JUDAH        3   2
   600 LAMOTTA      4   1
   600 LAMOTTA      4   2
   700 MARGARITO    3   1
   700 MARGARITO    3   2
   400 MARQUEZ      5   1
   400 MARQUEZ      5   2
   400 MAYWEATHER   5   1
   400 MAYWEATHER   5   2
   400 MOSLEY       5   1
   400 MOSLEY       5   2

如上所示，由于笛卡儿积的存在内嵌视图 X 的每一行数据都被返回了两次。不用着急，你很快就会明白为什么此处需要一个笛卡儿积。下一步是根据 ID（ID 的值为 1 或 2，这是由笛卡儿积生成的）为当前结果集中每个 DEPTNO 对应的员工进行编号。编号的结果显示在下面查询的输出部分。

select x.*, y.id,
       row_number()over(partition by x.deptno order by y.id) rn
  from (
select deptno deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_apps
       ) x,
       (select level id from dual connect by level <= 2) y
　
DEPTNO ENAME      CNT  ID         RN
------ ---------- --- --- ----------
   400 CORRALES     5   1          1
   400 MAYWEATHER   5   1          2
   400 CASTILLO     5   1          3
   400 MARQUEZ      5   1          4
   400 MOSLEY       5   1          5
   400 CORRALES     5   2          6
   400 MOSLEY       5   2          7
   400 MAYWEATHER   5   2          8
   400 CASTILLO     5   2          9
   400 MARQUEZ      5   2          10
   500 GATTI        2   1          1
   500 CALZAGHE     2   1          2
   500 GATTI        2   2          3
   500 CALZAGHE     2   2          4
   600 LAMOTTA      4   1          1
   600 HAGLER       4   1          2
   600 HEARNS       4   1          3
   600 FRAZIER      4   1          4
   600 LAMOTTA      4   2          5
   600 HAGLER       4   2          6
   600 FRAZIER      4   2          7
   600 HEARNS       4   2          8
   700 GUINN        3   1          1
   700 JUDAH        3   1          2
   700 MARGARITO    3   1          3
   700 GUINN        3   2          4
   700 JUDAH        3   2          5
   700 MARGARITO    3   2          6

每个员工都有了一个编号，并且，他们的重复项也都被分配了编号。上述结果集中包含了 IT_APP 表里的所有员工及其重复项，以及基于所属 DEPTNO 为每一行生成的编号。我们需要额外生成这些重复项的原因是，因为我们需要在结果集中留一个缝隙把 DEPTNO 插入到 ENAME 列。如果我们把一个只有 1 行数据的表和 IT_APPS 表连接起来做笛卡儿积，就无法得到这些额外的行。（因为一个表的记录条数乘以 1 的结果仍然会等于该表的记录条数。）
下一步是把到目前为止的结果集做行列翻转操作，这样 ENAMES 会以一列的形式返回，但会先返回他们所属的 DEPTNO。如下所示的查询展示了这一操作过程。

select 1 flag1, 1 flag2,
       decode(rn,1,to_char(deptno),' '||ename) it_dept
  from (
select x.*, y.id,
       row_number()over(partition by x.deptno order by y.id) rn
  from (
select deptno deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_apps
       ) x,
       (select level id from dual connect by level <= 2) y
       ) z
 where rn <= cnt+1
　
FLAG1      FLAG2 IT_DEPT
----- ---------- -------------------------
    1          1 400
    1          1   MAYWEATHER
    1          1   CASTILLO
    1          1   MARQUEZ
    1          1   MOSLEY
    1          1   CORRALES
    1          1 500
    1          1   CALZAGHE
    1          1   GATTI
    1          1 600
    1          1   HAGLER
    1          1   HEARNS
    1          1   FRAZIER
    1          1   LAMOTTA
    1          1 700
    1          1   JUDAH
    1          1   MARGARITO
    1          1   GUINN

先暂时忽略掉 FLAG1 和 FLAG2，稍后再做讨论。注意观察上述 IT_DEPT 列的结果。每个 DEPTNO 返回的记录行数是 CNT*2，但实际上只需要 CNT+1 行记录，WHERE 子句的过滤条件会限制记录行数。RN 是每个员工的编号，所有编号值小于或等于 CNT+1 的行都会被保留下来。也就是说，每个 DEPTNO 对应的所有员工再加上额外的 1 行记录会被保留下来（额外的那 1 行记录是每个 DEPTNO 对应的编号最小的员工）。这一行额外的记录就是用来插入 DEPTNO 的地方。调用 DECODE 函数（该函数的功能类似于 CASE 表达式，早期 Oracle 版本已经支持该函数）判定 RN 的值，并把 DEPTNO 插入到结果集里。RN 值等于 1 的员工不会被漏掉，但会被放在每个 DEPTNO 的最后位置（因为顺序无关紧要，放在最后也没有关系）。至此为止，我们已经详尽地讨论了 UNION ALL 的后半部分。
UNION ALL 的前半部分过程和后半部分相同，因此就没必要再重复讨论了。下面仔细观察一下两个查询结果叠加后的结果集。

select 1 flag1, 0 flag2,
       decode(rn,1,to_char(deptno),' '||ename) it_dept
  from (
select x.*, y.id,
       row_number()over(partition by x.deptno order by y.id) rn
  from (
select deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_research
       ) x,
       (select level id from dual connect by level <= 2) y
       )
 where rn <= cnt+1
union all
select 1 flag1, 1 flag2,
       decode(rn,1,to_char(deptno),' '||ename) it_dept
  from (
select x.*, y.id,
       row_number()over(partition by x.deptno order by y.id) rn
  from (
select deptno deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_apps
       ) x,
       (select level id from dual connect by level <= 2) y
       )
 where rn <= cnt+1
　
FLAG1      FLAG2 IT_DEPT
----- ---------- -----------------------
    1          0 100
    1          0   JONES
    1          0   TONEY
    1          0   HOPKINS
    1          0 200
    1          0   P.WHITAKER
    1          0   MARCIANO
    1          0   ROBINSON
    1          0   MORALES
    1          0 300
    1          0   WRIGHT
    1          0   J.TAYLOR
    1          0   LACY
    1          1 400
    1          1   MAYWEATHER
    1          1   CASTILLO
    1          1   MARQUEZ
    1          1   MOSLEY
    1          1   CORRALES
    1          1 500
    1          1   CALZAGHE
    1          1   GATTI
    1          1   600
    1          1   HAGLER
    1          1   HEARNS
    1          1   FRAZIER
    1          1   LAMOTTA
    1          1 700
    1          1   JUDAH
    1          1   MARGARITO
    1          1   GUINN

此时你或许还不明白 FLAG1 的作用，但可以看出 FLAG2 被用来标识行记录来自 UNION ALL 的哪个部分（0 表示前半部门，1 表示后半部分）。
下一步是把叠加后的结果集包裹在一个内嵌视图里，并计算 FLAG1 的累计合计值（终于知道它的作用了！），该累计合计值可以看作是 UNION ALL 的两个数据子集内部各自生成的行编号。编号后的结果集（累计合计值）如下所示。

select sum(flag1)over(partition by flag2
                          order by flag1,rownum) flag,
       it_dept, flag2
  from (
select 1 flag1, 0 flag2,
       decode(rn,1,to_char(deptno),' '||ename) it_dept
  from (
select x.*, y.id,
       row_number()over(partition by x.deptno order by y.id) rn
  from (
select deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_research
       ) x,
       (select level id from dual connect by level <= 2) y
       )
 where rn <= cnt+1
union all
select 1 flag1, 1 flag2,
       decode(rn,1,to_char(deptno),' '||ename) it_dept
  from (
select x.*, y.id,
       row_number()over(partition by x.deptno order by y.id) rn
  from (
select deptno deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_apps
       ) x,
       (select level id from dual connect by level <= 2) y
       )
 where rn <= cnt+1
       ) tmp1
　
FLAG IT_DEPT              FLAG2
---- --------------- ----------
   1 100                      0
   2   JONES                  0
   3   TONEY                  0
   4   HOPKINS                0
   5 200                      0
   6   P.WHITAKER             0
   7   MARCIANO               0
   8   ROBINSON               0
   9   MORALES                0
  10 300                      0
  11   WRIGHT                 0
  12   J.TAYLOR               0
  13   LACY                   0
   1 400                      1
   2   MAYWEATHER             1
   3   CASTILLO               1
   4   MARQUEZ                1
   5   MOSLEY                 1
   6   CORRALES               1
   7 500                      1
   8   CALZAGHE               1
   9   GATTI                  1
  10 600                      1
  11   HAGLER                 1
  12   HEARNS                 1
  13   FRAZIER                1
  14   LAMOTTA                1
  15 700                      1
  16   JUDAH                  1
  17   MARGARIT               1
  18   GUINN                  1

剩下的最后一步是，基于 FLAG2 把 TMP1 的返回值做行列翻转，同时也要按照 FLAG（TMP1 中生成的累计合计值）进行分组。把 TMP1 的查询结果包裹在一个内嵌视图（最外层的内嵌视图 TMP2）里，并做行列翻转。最终的解决方案和结果集显示如下。

select max(decode(flag2,0,it_dept)) research,
       max(decode(flag2,1,it_dept)) apps
  from (
select sum(flag1)over(partition by flag2
                          order by flag1,rownum) flag,
       it_dept, flag2
  from (
select 1 flag1, 0 flag2,
       decode(rn,1,to_char(deptno),' '||ename) it_dept
  from (
select x.*, y.id,
       row_number()over(partition by x.deptno order by y.id) rn
  from (
select deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_research
       ) x,
       (select level id from dual connect by level <= 2) y
       )
 where rn <= cnt+1
union all
select 1 flag1, 1 flag2,
       decode(rn,1,to_char(deptno),' '||ename) it_dept
  from (
select x.*, y.id,
       row_number()over(partition by x.deptno order by y.id) rn
  from (
select deptno deptno,
       ename,
       count(*)over(partition by deptno) cnt
  from it_apps
       ) x,
       (select level id from dual connect by level <= 2) y
       )
 where rn <= cnt+1
       ) tmp1
       ) tmp2
 group by flag
　
RESEARCH             APPS
-------------------- ---------------
100                  400
  JONES                MAYWEATHER
  TONEY                CASTILLO
  HOPKINS              MARQUEZ
200                    MOSLEY
  P.WHITAKER           CORRALES
  MARCIANO           500
  ROBINSON             CALZAGHE
  MORALES              GATTI
300                  600
  WRIGHT               HAGLER
  J.TAYLOR             HEARNS
  LACY                 FRAZIER
                       LAMOTTA
                     700
                       JUDAH
                       MARGARITO
                       GUINN