极客教程SQL 计算所有可能的表达式组合的小计，你想按照 DEPTNO、JOB 以及 JOB/DEPTNO 组合分别计算出工资合计值。同时，你也希望得到 EMP 表的工资总计。你希望得到如下所示的结果集。
SQL 计算所有可能的表达式组合的小计 问题描述
你想按照 DEPTNO、JOB 以及 JOB/DEPTNO 组合分别计算出工资合计值。同时，你也希望得到 EMP 表的工资总计。你希望得到如下所示的结果集。
DEPTNO JOB CATEGORY SAL
------ --------- --------------------- -------
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
30 SALESMAN TOTAL BY DEPT AND JOB 5600
30 MANAGER TOTAL BY DEPT AND JOB 2850
20 MANAGER TOTAL BY DEPT AND JOB 2975
20 ANALYST TOTAL BY DEPT AND JOB 6000
CLERK TOTAL BY JOB 4150
ANALYST TOTAL BY JOB 6000
MANAGER TOTAL BY JOB 8275
PRESIDENT TOTAL BY JOB 5000
SALESMAN TOTAL BY JOB 5600
10 TOTAL BY DEPT 8750
30 TOTAL BY DEPT 9400
20 TOTAL BY DEPT 10875
GRAND TOTAL FOR TABLE 29025
SQL 计算所有可能的表达式组合的小计 解决方案
近年来，对于 GROUP BY 语法的扩展使得本问题的解决变得容易多了。对于那些尚不支持这一类扩展语法的数据库，就必须（通过自连接或多个标量子查询）手动计算出多种层次的小计。
DB2
对于 DB2，需要把 GROUPING 函数的返回值转换为 CHAR(1) 类型。
1 select deptno,
2 job,
3 case cast(grouping(deptno) as char(1))||
4 cast(grouping(job) as char(1))
5 when '00' then 'TOTAL BY DEPT AND JOB'
6 when '10' then 'TOTAL BY JOB'
7 when '01' then 'TOTAL BY DEPT'
8 when '11' then 'TOTAL FOR TABLE'
9 end category,
10 sum(sal)
11 from emp
12 group by cube(deptno,job)
13 order by grouping(job),grouping(deptno)
Oracle
使用 GROUP BY 子句的 CUBE 扩展以及字符串连接操作符 ||。
1 select deptno,
2 job,
3 case grouping(deptno)||grouping(job)
4 when '00' then 'TOTAL BY DEPT AND JOB'
5 when '10' then 'TOTAL BY JOB'
6 when '01' then 'TOTAL BY DEPT'
7 when '11' then 'GRAND TOTAL FOR TABLE'
8 end category,
9 sum(sal) sal
10 from emp
11 group by cube(deptno,job)
12 order by grouping(job),grouping(deptno)
SQL Server
使用 GROUP BY 子句的 CUBE 扩展。对于 SQL Server，需要把 GROUPING 函数的返回值转换成 CHAR(1) 类型，并且要使用字符串连接操作符 +（不同于 Oracle 的||操作符）。
1 select deptno,
2 job,
3 case cast(grouping(deptno)as char(1))+
4 cast(grouping(job)as char(1))
5 when '00' then 'TOTAL BY DEPT AND JOB'
6 when '10' then 'TOTAL BY JOB'
7 when '01' then 'TOTAL BY DEPT'
8 when '11' then 'GRAND TOTAL FOR TABLE'
9 end category,
10 sum(sal) sal
11 from emp
12 group by deptno,job with cube
13 order by grouping(job),grouping(deptno)
PostgreSQL 和 MySQL
使用多个 UNION ALL，把每种类型的合计合并到一起。
1 select deptno, job,
2 'TOTAL BY DEPT AND JOB' as category,
3 sum(sal) as sal
4 from emp
5 group by deptno, job
6 union all
7 select null, job, 'TOTAL BY JOB', sum(sal)
8 from emp
9 group by job
10 union all
11 select deptno, null, 'TOTAL BY DEPT', sum(sal)
12 from emp
13 group by deptno
14 union all
15 select null,null,'GRAND TOTAL FOR TABLE', sum(sal)
16 from emp
SQL 计算所有可能的表达式组合的小计 扩展知识
Oracle、DB2 和 SQL Server
这 3 种数据库的解决方案大体相同。首先使用聚合函数 SUM，按照 DEPTNO 和 JOB 分组，找出每个 JOB 和 DEPTNO 组合对应的工资合计值。
select deptno, job, sum(sal) sal
from emp
group by deptno, job
DEPTNO JOB SAL
------ --------- -------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
下一步是生成 JOB 和 DEPTNO 的工资小计，以及全表的工资总计。使用 GROUP BY 子句的 CUBE 扩展，分别按照 DEPTNO、JOB 和全表的维度执行 SAL 列的聚合运算。
select deptno,
job,
sum(sal) sal
from emp
group by cube(deptno,job)
DEPTNO JOB SAL
------ --------- -------
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 10875
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
然后，使用 GROUPING 函数和 CASE 表达式把上述结果格式化为更有意义的输出。GROUPING(JOB) 的返回值应该是 1 或 0，这取决于 SAL 值是否来自 CUBE。如果结果来自 CUBE，则返回值为 0，否则返回 1。GROUPING(DEPTNO) 的返回值也是如此。再回头看一下本解决方案的第一步，我们知道它是按照 DEPTNO 和 JOB 进行分组的。因此，如果 SAL 值是基于 DEPTNO 和 JOB 组合计算而来的，那么 GROUPING 函数调用的返回值将是 0。执行结果显示如下。
select deptno,
job,
grouping(deptno) is_deptno_subtotal,
grouping(job) is_job_subtotal,
sum(sal) sal
from emp
group by cube(deptno,job)
order by 3,4
DEPTNO JOB IS_DEPTNO_SUBTOTAL IS_JOB_SUBTOTAL SAL
------ --------- ------------------ --------------- -------
10 CLERK 0 0 1300
10 MANAGER 0 0 2450
10 PRESIDENT 0 0 5000
20 CLERK 0 0 1900
30 CLERK 0 0 950
30 SALESMAN 0 0 5600
30 MANAGER 0 0 2850
20 MANAGER 0 0 2975
20 ANALYST 0 0 6000
10 0 1 8750
20 0 1 0875
30 0 1 9400
CLERK 1 0 4150
ANALYST 1 0 6000
MANAGER 1 0 8275
PRESIDENT 1 0 5000
SALESMAN 1 0 5600
1 1 9025
最后，使用 CASE 表达式确认每一行的归属，这是基于 GROUPING(JOB) 和 GROUPING(DEPTNO) 的返回值来决定的。
select deptno,
job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by cube(deptno,job)
order by grouping(job),grouping(deptno)
DEPTNO JOB CATEGORY SAL
------ --------- --------------------- -------
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
30 SALESMAN TOTAL BY DEPT AND JOB 5600
30 MANAGER TOTAL BY DEPT AND JOB 2850
20 MANAGER TOTAL BY DEPT AND JOB 2975
20 ANALYST TOTAL BY DEPT AND JOB 6000
CLERK TOTAL BY JOB 4150
ANALYST TOTAL BY JOB 6000
MANAGER TOTAL BY JOB 8275
PRESIDENT TOTAL BY JOB 5000
SALESMAN TOTAL BY JOB 5600
10 TOTAL BY DEPT 8750
30 TOTAL BY DEPT 9400
20 TOTAL BY DEPT 10875
GRAND TOTAL FOR TABLE 29025
上述 Oracle 解决方案在做字符串连接操作时，把两个 GROUPING 函数的返回值隐式地转换成了字符类型。对于 DB2 和 SQL Server 而言，则需要显式地把 GROUPING 函数的返回值转换成 CHAR(1) 数据类型，正如上述解决方案所示。另外，把两个 GROUPING 函数调用的返回值拼接成一个字符串时，SQL Server 用户必须使用 + 操作符，而不是||操作符。
对于 Oracle 和 DB2 而言，GROUP BY 还有一个 GROUPING SETS 语法扩展，该扩展也非常有用。例如，我们可以用 GROUPING SETS 模仿 CUBE 的输出结果，如下所示。（和 CUBE 解决方案一样，DB2 和 SQL Server 需要对 GROUPING 函数的返回值进行显式的数据类型转换。）
select deptno,
job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by grouping sets ((deptno),(job),(deptno,job),())
DEPTNO JOB CATEGORY SAL
------ --------- --------------------- -------
10 CLERK TOTAL BY DEPT AND JOB 1300
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
20 ANALYST TOTAL BY DEPT AND JOB 6000
10 MANAGER TOTAL BY DEPT AND JOB 2450
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
CLERK TOTAL BY JOB 4150
ANALYST TOTAL BY JOB 6000
MANAGER TOTAL BY JOB 8275
SALESMAN TOTAL BY JOB 5600
PRESIDENT TOTAL BY JOB 5000
10 TOTAL BY DEPT 8750
20 TOTAL BY DEPT 10875
30 TOTAL BY DEPT 9400
GRAND TOTAL FOR TABLE 29025
GROUPING SETS 的奇妙之处在于它允许我们定义分组。上述查询中的 GROUPING SETS 子句分别指定了按照 DEPTNO 分组，按照 JOB 分组，按照 DEPTNO 和 JOB 的组合分组，以及最后空白括号代表的总计。GROUPING SETS 能够非常灵活地支持不同维度的聚合运算。例如，如果我们希望改一下上面的例子，去掉 GRAND TOTAL，那么只要把 GROUPING SETS 子句的空白括号删除掉就可以了。
/*去掉总计 */
select deptno,
job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by grouping sets ((deptno),(job),(deptno,job))
DEPTNO JOB CATEGORY SAL
------ --------- --------------------- ----------
10 CLERK TOTAL BY DEPT AND JOB 1300
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
20 ANALYST TOTAL BY DEPT AND JOB 6000
10 MANAGER TOTAL BY DEPT AND JOB 2450
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
CLERK TOTAL BY JOB 4150
ANALYST TOTAL BY JOB 6000
MANAGER TOTAL BY JOB 8275
SALESMAN TOTAL BY JOB 5600
PRESIDENT TOTAL BY JOB 5000
10 TOTAL BY DEPT 8750
20 TOTAL BY DEPT 10875
30 TOTAL BY DEPT 9400
我们也可以删除一个小计，例如基于 DEPTNO 的小计，只要从 GROUPING SETS 子句中去掉“(DEPTNO)”就可以了。
/* 去掉DEPTNO小计 */
select deptno,
job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by grouping sets ((job),(deptno,job),())
order by 3
DEPTNO JOB CATEGORY SAL
------ --------- --------------------- ----------
GRAND TOTAL FOR TABLE 29025
10 CLERK TOTAL BY DEPT AND JOB 1300
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
20 ANALYST TOTAL BY DEPT AND JOB 6000
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
10 MANAGER TOTAL BY DEPT AND JOB 2450
CLERK TOTAL BY JOB 4150
SALESMAN TOTAL BY JOB 5600
PRESIDENT TOTAL BY JOB 5000
MANAGER TOTAL BY JOB 8275
ANALYST TOTAL BY JOB 6000
如上所述，GROUPING SETS 确实能更方便地帮助我们从不同角度获得总计和小计。
PostgreSQL 和 MySQL
首先使用聚合函数 SUM，并按照 DEPTNO 和 JOB 进行分组。
select deptno, job,
'TOTAL BY DEPT AND JOB' as category,
sum(sal) as sal
from emp
group by deptno, job
DEPTNO JOB CATEGORY SAL
------ --------- --------------------- -------
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
20 ANALYST TOTAL BY DEPT AND JOB 6000
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 CLERK TOTAL BY DEPT AND JOB 950
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
然后，使用 UNION ALL 把基于 JOB 分组的工资合计值合并进来。
select deptno, job,
'TOTAL BY DEPT AND JOB' as category,
sum(sal) as sal
from emp
group by deptno, job
union all
select null, job, 'TOTAL BY JOB', sum(sal)
from emp
group by job
DEPTNO JOB CATEGORY SAL
------ --------- --------------------- -------
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
20 ANALYST TOTAL BY DEPT AND JOB 6000
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 CLERK TOTAL BY DEPT AND JOB 950
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
ANALYST TOTAL BY JOB 6000
CLERK TOTAL BY JOB 4150
MANAGER TOTAL BY JOB 8275
PRESIDENT TOTAL BY JOB 5000
SALESMAN TOTAL BY JOB 5600
再使用 UNION ALL 把基于 DEPTNO 分组的工资合计值合并进来。
select deptno, job,
'TOTAL BY DEPT AND JOB' as category,
sum(sal) as sal
from emp
group by deptno, job
union all
select null, job, 'TOTAL BY JOB', sum(sal)
from emp
group by job
union all
select deptno, null, 'TOTAL BY DEPT', sum(sal)
from emp
group by deptno
DEPTNO JOB CATEGORY SAL
------ --------- --------------------- -------
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
20 ANALYST TOTAL BY DEPT AND JOB 6000
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 CLERK TOTAL BY DEPT AND JOB 950
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
ANALYST TOTAL BY JOB 6000
CLERK TOTAL BY JOB 4150
MANAGER TOTAL BY JOB 8275
PRESIDENT TOTAL BY JOB 5000
SALESMAN TOTAL BY JOB 5600
10 TOTAL BY DEPT 8750
20 TOTAL BY DEPT 10875
30 TOTAL BY DEPT 9400
最后，使用 UNION ALL 把 EMP 表的工资总计合并进来。
select deptno, job,
'TOTAL BY DEPT AND JOB' as category,
sum(sal) as sal
from emp
group by deptno, job
union all
select null, job, 'TOTAL BY JOB', sum(sal)
from emp
group by job
union all
select deptno, null, 'TOTAL BY DEPT', sum(sal)
from emp
group by deptno
union all
select null,null, 'GRAND TOTAL FOR TABLE', sum(sal)
from emp
DEPTNO JOB CATEGORY SAL
------ --------- --------------------- -------
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
20 ANALYST TOTAL BY DEPT AND JOB 6000
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 CLERK TOTAL BY DEPT AND JOB 950
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
ANALYST TOTAL BY JOB 6000
CLERK TOTAL BY JOB 4150
MANAGER TOTAL BY JOB 8275
PRESIDENT TOTAL BY JOB 5000
SALESMAN TOTAL BY JOB 5600
10 TOTAL BY DEPT 8750
20 TOTAL BY DEPT 10875
30 TOTAL BY DEPT 9400
GRAND TOTAL FOR TABLE 29025