SQL 识别非小计行|极客教程

SQL 识别非小计行，你已经知道如何使用 GROUP BY 子句的 CUBE 扩展语法生成报表，并且你需要知道如何区分哪些行是由普通的 GROUP BY 子句产生的，哪些行是由 CUBE 或 ROLLUP 产生的。

SQL 识别非小计行问题描述

你已经知道如何使用 GROUP BY 子句的 CUBE 扩展语法生成报表，并且你需要知道如何区分哪些行是由普通的 GROUP BY 子句产生的，哪些行是由 CUBE 或 ROLLUP 产生的。
下面给出了一组利用 GROUP BY 的 CUBE 扩展语法生成的查询结果集，它是 EMP 表中员工工资的一个分类汇总结果。

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

以上报表展示了按照 DEPTNO 和 JOB 分组计算出来的（每个 DEPTNO 对应的每一种 JOB 的）工资合计值，按照 DEPTNO 分组计算出来的工资合计值，按照 JOB 分组计算出来的工资合计值，以及最后的总计（EMP 表的工资合计值）。你希望能清楚地识别上述各种聚合运算的维度，并标记出每一个聚合运算结果分别属于哪一类。（也就是说，给定的 SAL 值代表的是按照 DEPTNO 分组计算的结果，还是按照 JOB 分组计算的结果，或者是总计？）你希望返回如下所示的结果集。

DEPTNO JOB           SAL DEPTNO_SUBTOTALS JOB_SUBTOTALS
------ --------- ------- ---------------- -------------
                   29025                1             1
       CLERK        4150                1             0
       ANALYST      6000                1             0
       MANAGER      8275                1             0
       SALESMAN     5600                1             0
       PRESIDENT    5000                1             0
    10              8750                0             1
    10 CLERK        1300                0             0
    10 MANAGER      2450                0             0
    10 PRESIDENT    5000                0             0
    20             10875                0             1
    20 CLERK        1900                0             0
    20 ANALYST      6000                0             0
    20 MANAGER      2975                0             0
    30              9400                0             1
    30 CLERK         950                0             0
    30 MANAGER      2850                0             0
    30 SALESMAN     5600                0             0

SQL 识别非小计行解决方案

使用 GROUPING 函数判断哪些值是 CUBE 或 ROLLUP 的小计结果，即超级聚合（supera ggregate）值。下面是 DB2 和 Oracle 的示例代码。

1  select deptno, job, sum(sal) sal,
2         grouping(deptno) deptno_subtotals,
3         grouping(job) job_subtotals
4    from emp
5   group by cube(deptno,job)

相较于 DB2 和 Oracle 解决方案，SQL Server 解决方案唯一的不同之处在于 CUBE/ROLLUP 子句的语法。

1  select deptno, job, sum(sal) sal,
2         grouping(deptno) deptno_subtotals,
3         grouping(job) job_subtotals
4    from emp
5   group by deptno,job with cube

本实例的重点在于展示如何使用 CUBE 和 GROUPING 处理小计计算。在写作本书时，PostgreSQL 和 MySQL 尚不支持 CUBE 或 GROUPING。

SQL 识别非小计行扩展知识

如果 DEPTNO_SUBTOTALS 等于 0，并且 JOB_SUBTOTALS 等于 1（此时 JOB 是 Null），那么 SAL 值就是 CUBE 查询生成的、按照 DEPTNO 分组的小计结果。如果 JOB_SUBTOTALS 等于 0，并且 DEPTNO_SUBTOTALS 等于 1（此时 DEPTNO 是 Null），那么 SAL 值就是 CUBE 查询生成的、按照 JOB 分组的小计结果。如果 JOB_SUBTOTALS 和 DEPTNO_SUBTOTALS 都等于 1，那么 SAL 值就是 CUBE 查询生成的工资总计。DEPTNO_SUBTOTALS 和 JOB_SUBTOTALS 都等于 0 的行则是通常的聚合运算结果（每个 DEPTNO/JOB 组合对应的 SAL 合计）。