SQL 计算简单的小计|极客教程

SQL 计算简单的小计，在本实例中，“简单的小计”指的是一种特殊的结果集，该结果集不仅包括某一列的聚合运算结果，也包括了整个表中该列的合计值。例如，一个结果集里既包括了 EMP 表各个 JOB 对应的工资合计值，也包括了全部工资的总计。EMP 表各个 JOB 对应的工资合计值是小计，全部工资的合计值是总计。

SQL 计算简单的小计问题描述

在本实例中，“简单的小计”指的是一种特殊的结果集，该结果集不仅包括某一列的聚合运算结果，也包括了整个表中该列的合计值。例如，一个结果集里既包括了 EMP 表各个 JOB 对应的工资合计值，也包括了全部工资的总计。EMP 表各个 JOB 对应的工资合计值是小计，全部工资的合计值是总计。上述结果集看起来应该如下所示。

JOB              SAL
--------- ----------
ANALYST         6000
CLERK           4150
MANAGER         8275
PRESIDENT       5000
SALESMAN        5600
TOTAL          29025

SQL 计算简单的小计解决方案

针对 GROUP BY 子句的 ROLLUP 扩展完美地解决了本问题。对于不支持 ROLLUP 的数据库，可以借助标量子查询或 UNION 查询解决本问题，当然做法会繁琐一些。
DB2 和 Oracle
使用聚合函数 SUM 计算工资合计值，并使用 GROUP BY 的 ROLLUP 扩展构造出同时包含小计（按 JOB 分区）和总计（针对全表数据）的结果集。

1 select case grouping(job)
2             when 0 then job
3             else 'TOTAL'
4        end job,
5        sum(sal) sal
6   from emp
7  group by rollup(job)

SQL Server 和 MySQL
使用聚合函数 SUM 计算工资合计值，并使用 WITH ROLLUP 构造出同时包含小计（按 JOB 分区）和总计（针对全表数据）的结果集。然后调用 COALESCE 函数把总计行的标题改为 TOTAL（否则这一行的 JOB 列会出现 Null 值）。

1 select coalesce(job,'TOTAL') job,
2        sum(sal) sal
3    from emp
4   group by job with rollup

如果是 SQL Server，也可以不使用 COALESCE 函数，我们可以像上述 Oracle 和 DB2 的解决方案那样使用 GROUPING 函数来判断聚合运算的层级。
PostgreSQL
使用聚合函数 SUM 计算各个 DEPTNO 的工资合计值，然后使用 UNION ALL 把该查询和生成全表的工资总计的查询连在一起。

1 select job, sum(sal) as sal
2   from emp
3  group by job
4  union all
5 select 'TOTAL', sum(sal)
6   from emp

SQL 计算简单的小计扩展知识

DB2 和 Oracle
首先使用聚合函数 SUM，按照 JOB 分组并生成各个 JOB 的工资合计值。

select job, sum(sal) sal
  from emp
 group by job
　
JOB         SAL
--------- -----
ANALYST    6000
CLERK      4150
MANAGER    8275
PRESIDENT  5000
SALESMAN   5600

然后，使用 GROUP BY 的 ROLLUP 扩展在各个 JOB 的工资小计之外，再生成一个工资总计。

select job, sum(sal) sal
  from emp
 group by rollup(job)
　
JOB           SAL
--------- -------
ANALYST      6000
CLERK        4150
MANAGER      8275
PRESIDENT    5000
SALESMAN     5600
            29025

最后，借助 GROUPING 函数把工资总计行对应的 JOB 列的显示内容修改一下。如果 JOB 值是 Null，那么 GROUPING 函数会返回 1，这意味着 SAL 值是由 ROLLUP 生成的工资总计。如果 JOB 值不为 Null，则 GROUPING 函数将返回 0，这意味着 SAL 值是 GROUP BY 查询的结果，而不是 ROLLUP 的结果。在 CASE 表达式中调用 GROUPING(JOB)，这样就能根据需要返回具体的职位或代表总计行的标签 TOTAL。

select case grouping(job)
            when 0 then job
            else 'TOTAL'
        end job,
        sum(sal) sal
  from emp
 group by rollup(job)
　
JOB              SAL
--------- ----------
ANALYST         6000
CLERK           4150
MANAGER         8275
PRESIDENT       5000
SALESMAN        5600
TOTAL          29025

SQL Server 和 MySQL
首先使用聚合函数 SUM，按照 JOB 分组生成各个 JOB 的工资合计值。

select job, sum(sal) sal
  from emp
 group by job
　
JOB         SAL
--------- -----
ANALYST    6000
CLERK      4150
MANAGER    8275
PRESIDENT  5000
SALESMAN   5600

然后，使用 GROUP BY 的 ROLLUP 扩展在各个 JOB 的工资小计之外，再生成一个工资总计。

select job, sum(sal) sal
  from emp
 group by job with rollup
　
JOB           SAL
--------- -------
ANALYST      6000
CLERK        4150
MANAGER      8275
PRESIDENT    5000
SALESMAN     5600
            29025

最后，针对 JOB 列调用 COEALESCE 函数。如果 JOB 值为 Null，SAL 值就是由 ROLLUP 生成的工资总计。如果 JOB 值不为 Null，则 SAL 值是由通常的 GROUP BY 产生的结果，而不是 ROLLUP 的结果。

select coalesce(job,'TOTAL') job,
       sum(sal) sal
  from emp
 group by job with rollup
　
JOB              SAL
--------- ----------
ANALYST         6000
CLERK           4150
MANAGER         8275
PRESIDENT       5000
SALESMAN        5600
TOTAL          29025

PostgreSQL
首先对结果按照 JOB 分组，并使用聚合函数 SUM 生成各个 JOB 的工资合计值。

select job, sum(sal) sal
  from emp
 group by job
　
JOB         SAL
--------- -----
ANALYST    6000
CLERK      4150
MANAGER    8275
PRESIDENT  5000
SALESMAN   5600

最后，在上述查询的基础上使用 UNION ALL 查询生成的工资总计。

select job, sum(sal) as sal
  from emp
 group by job
 union all
select 'TOTAL', sum(sal)
  from emp
　
JOB           SAL
--------- -------
ANALYST      6000
CLERK        4150
MANAGER      8275
PRESIDENT    5000
SALESMAN     5600
TOTAL       29025