在GROUPBY子句中使用 GROUPING SETS 扩展提供了生成一个结果集的方法,该结果集实际上是基于不同分组的多个结果集的串联。换句话说,就是执行一项 UNION ALL 运算,将多个分组的结果集合并到一个结果集中。

您会看到,UNION ALL 运算以及 GROUPINGSETS 扩展不会从合并在一起的结果集中消除重复行。

单个 GROUPINGSETS 扩展的语法如下:

GROUPING SETS (
  { expr_1 | ( expr_1a [, expr_1b ] ...) |
    ROLLUP ( expr_list ) | CUBE ( expr_list )
  } [, ...] )

GROUPING SETS 扩展可包含一个或多个逗号分隔表达式的任意组合、带圆括号的表达式列表、ROLLUP 扩展和 CUBE 扩展。

GROUPING SETS 扩展在 GROUP BY 子句中指定,如下所示:

SELECT select_list FROM ...
GROUP BY [... ,] GROUPING SETS ( expression_list ) [, ...]

select_list 中指定的各项也必须出现在 GROUPING SETS expression_list 中;或者这些项必须是聚合函数,如 COUNT、SUM、AVG、MIN 或 MAX;或者这些项必须是其返回值独立于组中各行的约束或函数(例如,SYSDATE 函数)。

GROUP BY 子句可指定多个 GROUPING SETS 扩展以及多次出现的其他 GROUP BY 扩展和各表达式。

如果您希望将输出显示在一个有意义的结构中,应使用 ORDER BY 子句。如果未指定 ORDER BY 子句,则无法保证结果集的顺序。

以下查询生成由 loc、dname 和 job 列指定的组的联合。

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY GROUPING SETS (loc, dname, job)
ORDER BY 1, 2, 3;

结果所下:

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   |            |           |         8
 CHICAGO  |            |           |         6
 NEW YORK |            |           |         3
          | ACCOUNTING |           |         3
          | OPERATIONS |           |         3
          | RESEARCH   |           |         5
          | SALES      |           |         6
          |            | ANALYST   |         3
          |            | CLERK     |         5
          |            | MANAGER   |         4
          |            | PRESIDENT |         1
          |            | SALESMAN  |         4
(12 rows)

这等同于使用 UNION ALL 运算符的以下查询。

SELECT loc AS "loc", NULL AS "dname", NULL AS "job", COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY loc
  UNION ALL
SELECT NULL, dname, NULL, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname
  UNION ALL
SELECT NULL, NULL, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY job
ORDER BY 1, 2, 3;

UNION ALL 查询的输出与 GROUPING SETS 输出相同。

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   |            |           |         8
 CHICAGO  |            |           |         6
 NEW YORK |            |           |         3
          | ACCOUNTING |           |         3
          | OPERATIONS |           |         3
          | RESEARCH   |           |         5
          | SALES      |           |         6
          |            | ANALYST   |         3
          |            | CLERK     |         5
          |            | MANAGER   |         4
          |            | PRESIDENT |         1
          |            | SALESMAN  |         4
(12 rows)

以下示例显示如何在 GROUPING SETS 表达式列表中将各种类型的 GROUP BY 扩展一起使用。

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY GROUPING SETS (loc, ROLLUP (dname, job), CUBE (job, loc))
ORDER BY 1, 2, 3;

下面是此查询的输出。

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   |            | ANALYST   |         3
 BOSTON   |            | CLERK     |         3
 BOSTON   |            | MANAGER   |         2
 BOSTON   |            |           |         8
 BOSTON   |            |           |         8
 CHICAGO  |            | CLERK     |         1
 CHICAGO  |            | MANAGER   |         1
 CHICAGO  |            | SALESMAN  |         4
 CHICAGO  |            |           |         6
 CHICAGO  |            |           |         6
 NEW YORK |            | CLERK     |         1
 NEW YORK |            | MANAGER   |         1
 NEW YORK |            | PRESIDENT |         1
 NEW YORK |            |           |         3
 NEW YORK |            |           |         3
          | ACCOUNTING | CLERK     |         1
          | ACCOUNTING | MANAGER   |         1
          | ACCOUNTING | PRESIDENT |         1
          | ACCOUNTING |           |         3
          | OPERATIONS | ANALYST   |         1
          | OPERATIONS | CLERK     |         1
          | OPERATIONS | MANAGER   |         1
          | OPERATIONS |           |         3
          | RESEARCH   | ANALYST   |         2
          | RESEARCH   | CLERK     |         2
          | RESEARCH   | MANAGER   |         1
          | RESEARCH   |           |         5
          | SALES      | CLERK     |         1
          | SALES      | MANAGER   |         1
          | SALES      | SALESMAN  |         4
          | SALES      |           |         6
          |            | ANALYST   |         3
          |            | CLERK     |         5
          |            | MANAGER   |         4
          |            | PRESIDENT |         1
          |            | SALESMAN  |         4
          |            |           |        17
          |            |           |        17
(38 rows)

该输出基本上是结果集的串联,这些结果集是分别从 GROUP BY loc、GROUP BY ROLLUP (dname, job) 和 GROUP BY CUBE (job, loc) 生成的。下面显示了其中的各个查询。

SELECT loc, NULL AS "dname", NULL AS "job", COUNT(*) AS "employees"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY loc
ORDER BY 1;

下面是从 GROUP BY loc 子句生成的结果集。

   loc    | dname | job | employees
----------+-------+-----+-----------
 BOSTON   |       |     |         8
 CHICAGO  |       |     |         6
 NEW YORK |       |     |         3
(3 rows)

以下查询使用 GROUP BY ROLLUP (dname, job) 子句。

SELECT NULL AS "loc", dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (dname, job)
ORDER BY 2, 3;

下面是从 GROUP BY ROLLUP (dname, job) 子句生成的结果集。

 loc |   dname    |    job    | employees
-----+------------+-----------+-----------
     | ACCOUNTING | CLERK     |         1
     | ACCOUNTING | MANAGER   |         1
     | ACCOUNTING | PRESIDENT |         1
     | ACCOUNTING |           |         3
     | OPERATIONS | ANALYST   |         1
     | OPERATIONS | CLERK     |         1
     | OPERATIONS | MANAGER   |         1
     | OPERATIONS |           |         3
     | RESEARCH   | ANALYST   |         2
     | RESEARCH   | CLERK     |         2
     | RESEARCH   | MANAGER   |         1
     | RESEARCH   |           |         5
     | SALES      | CLERK     |         1
     | SALES      | MANAGER   |         1
     | SALES      | SALESMAN  |         4
     | SALES      |           |         6
     |            |           |        17
(17 rows)

以下查询使用 GROUP BY CUBE (job, loc) 子句。

SELECT loc, NULL AS "dname", job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (job, loc)
ORDER BY 1, 3;

下面是从 GROUP BY CUBE (job, loc) 子句生成的结果集。

   loc    | dname |    job    | employees
----------+-------+-----------+-----------
 BOSTON   |       | ANALYST   |         3
 BOSTON   |       | CLERK     |         3
 BOSTON   |       | MANAGER   |         2
 BOSTON   |       |           |         8
 CHICAGO  |       | CLERK     |         1
 CHICAGO  |       | MANAGER   |         1
 CHICAGO  |       | SALESMAN  |         4
 CHICAGO  |       |           |         6
 NEW YORK |       | CLERK     |         1
 NEW YORK |       | MANAGER   |         1
 NEW YORK |       | PRESIDENT |         1
 NEW YORK |       |           |         3
          |       | ANALYST   |         3
          |       | CLERK     |         5
          |       | MANAGER   |         4
          |       | PRESIDENT |         1
          |       | SALESMAN  |         4
          |       |           |        17
(18 rows)

如果使用 UNION ALL 运算符将前三个查询合并在一起,将生成三个结果集的串联。

SELECT loc AS "loc", NULL AS "dname", NULL AS "job", COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY loc
  UNION ALL
SELECT NULL, dname, job, count(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (dname, job)
  UNION ALL
SELECT loc, NULL, job, count(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (job, loc)
ORDER BY 1, 2, 3;

下面是生成的输出,这与使用 GROUP BY GROUPING SETS (loc, ROLLUP (dname, job), CUBE(job, loc)) 子句时生成的输出相同。

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   |            | ANALYST   |         3
 BOSTON   |            | CLERK     |         3
 BOSTON   |            | MANAGER   |         2
 BOSTON   |            |           |         8
 BOSTON   |            |           |         8
 CHICAGO  |            | CLERK     |         1
 CHICAGO  |            | MANAGER   |         1
 CHICAGO  |            | SALESMAN  |         4
 CHICAGO  |            |           |         6
 CHICAGO  |            |           |         6
 NEW YORK |            | CLERK     |         1
 NEW YORK |            | MANAGER   |         1
 NEW YORK |            | PRESIDENT |         1
 NEW YORK |            |           |         3
 NEW YORK |            |           |         3
          | ACCOUNTING | CLERK     |         1
          | ACCOUNTING | MANAGER   |         1
          | ACCOUNTING | PRESIDENT |         1
          | ACCOUNTING |           |         3
          | OPERATIONS | ANALYST   |         1
          | OPERATIONS | CLERK     |         1
          | OPERATIONS | MANAGER   |         1
          | OPERATIONS |           |         3
          | RESEARCH   | ANALYST   |         2
          | RESEARCH   | CLERK     |         2
          | RESEARCH   | MANAGER   |         1
          | RESEARCH   |           |         5
          | SALES      | CLERK     |         1
          | SALES      | MANAGER   |         1
          | SALES      | SALESMAN  |         4
          | SALES      |           |         6
          |            | ANALYST   |         3
          |            | CLERK     |         5
          |            | MANAGER   |         4
          |            | PRESIDENT |         1
          |            | SALESMAN  |         4
          |            |           |        17
          |            |           |        17
(38 rows)