极客教程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