SQL 变换带有小计的结果集|极客教程

SQL 变换带有小计的结果集，你想创建一个包含小计的报表，并希望把该结果重新格式化，使之更具可读性。例如，你已经按照要求创建了一张报表，其中包含每个部门的所有管理者，以及每个管理者下属所有员工的工资合计值。

SQL 变换带有小计的结果集问题描述

你想创建一个包含小计的报表，并希望把该结果重新格式化，使之更具可读性。例如，你已经按照要求创建了一张报表，其中包含每个部门的所有管理者，以及每个管理者下属所有员工的工资合计值。同时，你也希望能看到两种额外的小计：去掉管理者之后、每个部门的所有下属员工的工资总额，以及上述结果集中所有工资的合计值（在部门工资小计的基础上再求和，得出总计）。现在，你得到的报表如下所示。

DEPTNO        MGR        SAL
------ ---------- ----------
    10       7782       1300
    10       7839       2450
    10                  3750
    20       7566       6000
    20       7788       1100
    20       7839       2975
    20       7902        800
    20                 10875
    30       7698       6550
    30       7839       2850
    30                  9400
                       24025

你想提供一个更具可读性的报表，因此希望把上述结果转换成如下所示的形式，以便更清楚明白地表达每个小计的含义。

MGR      DEPT10     DEPT20     DEPT30      TOTAL
---- ---------- ---------- ---------- ----------
7566          0       6000          0
7698          0          0       6550
7782       1300          0          0
7788          0       1100          0
7839       2450       2975       2850
7902          0        800          0
           3750      10875       9400      24025

SQL 变换带有小计的结果集解决方案

首先使用 GROUP BY 的 ROLLUP 扩展生成小计，接着执行一次结果集变换（使用聚合运算和 CASE 表达式）以创建出新报表所需的列。GROUPING 函数能帮助我们方便地识别出哪些是小计（也就是说，小计都是由 ROLLUP 产生出来的，正常的 GROUP BY 无法生成小计）。由于不同的数据库对 Null 排序的处理方式各有不同，我们可能需要为某些解决方案增加 ORDER BY，以便最终的结果集和上述目标结果集相一致。
DB2 和 Oracle
使用 GROUP BY 的 ROLLUP 扩展，然后借助 CASE 表达式把数据格式化成更具可读性的报表。

 1  select mgr,
 2         sum(case deptno when 10 then sal else 0 end) dept10,
 3         sum(case deptno when 20 then sal else 0 end) dept20,
 4         sum(case deptno when 30 then sal else 0 end) dept30,
 5         sum(case flag when '11' then sal else null end) total
 6    from (
 7  select deptno,mgr,sum(sal) sal,
 8         cast(grouping(deptno) as char(1))||
 9         cast(grouping(mgr) as char(1)) flag
10    from emp
11   where mgr is not null
12   group by rollup(deptno,mgr)
13         ) x
14   group by mgr

SQL Server
使用 GROUP BY 的 ROLLUP 扩展，然后借助 CASE 表达式把数据格式化成更具可读性的报表。

 1  select mgr,
 2         sum(case deptno when 10 then sal else 0 end) dept10,
 3         sum(case deptno when 20 then sal else 0 end) dept20,
 4         sum(case deptno when 30 then sal else 0 end) dept30,
 5         sum(case flag   when '11' then sal else null end) total
 6    from (
 7  select deptno,mgr,sum(sal) sal,
 8         cast(grouping(deptno) as char(1))+
 9         cast(grouping(mgr)    as char(1)) flag
10    from emp
11   where mgr is not null
12   group by deptno,mgr with rollup
13         ) x
14   group by mgr

MySQL 和 PostgreSQL
这两种数据库都不支持 GROUPING 函数。

SQL 变换带有小计的结果集扩展知识

以上两种解决方案大致相同，但在字符串连接和 GROUPING 函数调用的方式上略有不同。由于它们非常相似，下面的讨论部分在展示中间结果的时候将以 SQL Server 解决方案为准（也会兼顾 DB2 和 Oracle）。
首先为每个 DEPTNO 的每个 MGR 计算出其下属员工的 SAL 合计值。基本的想法是希望打印出某个部门特定管理者下属所有员工的工资合计值。例如，下面的查询可以比较 KING 下属的 DEPTNO 10 和 DEPTNO 30 的所有员工的工资。

select deptno,mgr,sum(sal) sal
  from emp
 where mgr is not null
 group by mgr,deptno
 order by 1,2
　
DEPTNO        MGR        SAL
------ ---------- ----------
    10       7782       1300
    10       7839       2450
    20       7566       6000
    20       7788       1100
    20       7839       2975
    20       7902        800
    30       7698       6550
    30       7839       2850

然后使用 GROUP BY 的 ROLLUP 扩展，计算每个 DEPTNO 对应的工资小计以及全体员工（不包括最高管理者）的工资总额。

select deptno,mgr,sum(sal) sal
  from emp
 where mgr is not null
 group by deptno,mgr with rollup
　
DEPTNO        MGR        SAL
------ ---------- ----------
    10       7782       1300
    10       7839       2450
    10                  3750
    20       7566       6000
    20       7788       1100
    20       7839       2975
    20       7902        800
    20                 10875
    30       7698       6550
    30       7839       2850
    30                  9400
                       24025

计算出了小计之后，我们需要知道如何判断哪些值是（由 ROLLUP 产生的）小计，哪些值是由正常的 GROUP BY 产生的结果。使用 GROUPING 函数创建位图可以把小计从正常的聚合运算值中分离出来。

select deptno,mgr,sum(sal) sal,
       cast(grouping(deptno) as char(1))+
       cast(grouping(mgr)    as char(1)) flag
  from emp
 where mgr is not null
 group by deptno,mgr with rollup
　
DEPTNO        MGR        SAL FLAG
------ ---------- ---------- ----
    10       7782       1300 00
    10       7839       2450 00
    10                  3750 01
    20       7566       6000 00
    20       7788       1100 00
    20       7839       2975 00
    20       7902        800 00
    20                 10875 01
    30       7698       6550 00
    30       7839       2850 00
    30                  9400 01
                       24025 11

如果要讲得更清楚明白一点的话，就是 FLAG 值等于 00 的行都是正常的聚合运算结果。FLAG 值等于 01 的行是由 ROLLUP 生成的、每个 DEPTNO 的 SAL 合计值（因为 DEPTNO 是 ROLLUP 的第一个参数；如果变换一下顺序，例如改为“GROUP BY MGR, DEPTNO WITH ROLLUP”，结果会大相径庭）。FLAG 值等于 11 的行是由 ROLLUP 产生的、所有 SAL 的合计值。
现在，所需的数据都已经准备好了，接下来要使用 CASE 表达式把它们重新格式化为一个更直观的结果集。我们的目标是为跨部门的每位管理者显示其下属员工的工资合计值。如果一位管理者在某个部门没有下属员工，那就返回 0；否则，将返回该管理者在给定部门所有下属员工的工资合计值。除此之外，我们还要在报表的最后面增加一个 TOTAL 列以打印报表中全部工资的总计。满足上述所有要求的解决方案如下所示。

select mgr,
       sum(case deptno when 10 then sal else 0 end) dept10,
       sum(case deptno when 20 then sal else 0 end) dept20,
       sum(case deptno when 30 then sal else 0 end) dept30,
       sum(case flag   when '11' then sal else null end) total
  from (
select deptno,mgr,sum(sal) sal,
       cast(grouping(deptno) as char(1))+
       cast(grouping(mgr)    as char(1)) flag
  from emp
 where mgr is not null
 group by deptno,mgr with rollup
       ) x
 group by mgr
 order by coalesce(mgr,9999)
　
MGR      DEPT10     DEPT20     DEPT30      TOTAL
---- ---------- ---------- ---------- ----------
7566          0       6000          0
7698          0          0       6550
7782       1300          0          0
7788          0       1100          0
7839       2450       2975       2850
7902          0        800          0
           3750      10875       9400      24025