通过grouping sets 设置多个分组,网上看到的一个例子:
SQL> SELECT CASE
2 WHEN a.deptno IS NULL THEN
3 '合计'
4 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN
5 '小计'
6 ELSE
7 '' || a.deptno
8 END deptno,
9 a.empno,
10 a.ename,
11 SUM(a.sal) total_sal
12 FROM scott.emp a
13 GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());
DEPTNO EMPNO ENAME TOTAL_SAL
---------------------------------------- ----- ---------- ----------
10 7782 CLARK 2450
10 7839 KING 5000
10 7934 MILLER 1300
小计 8750
20 7369 SMITH 800
20 7566 JONES 2975
20 7788 SCOTT 3000
20 7876 ADAMS 1100
20 7902 FORD 3000
小计 10875
30 7900 JAMES 950
30 7499 ALLEN 1600
30 7521 WARD 1250
30 7654 MARTIN 1250
30 7698 BLAKE 2850
30 7844 TURNER 1500
小计 9400
合计 29025
SQL> SELECT CASE
2 WHEN a.deptno IS NULL THEN
3 '合计'
4 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN
5 '小计'
6 ELSE
7 '' || a.deptno
8 END deptno,
9 a.empno,
10 a.ename,
11 SUM(a.sal) total_sal
12 FROM scott.emp a
13 GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());
DEPTNO EMPNO ENAME TOTAL_SAL
---------------------------------------- ----- ---------- ----------
10 7782 CLARK 2450
10 7839 KING 5000
10 7934 MILLER 1300
小计 8750
20 7369 SMITH 800
20 7566 JONES 2975
20 7788 SCOTT 3000
20 7876 ADAMS 1100
20 7902 FORD 3000
小计 10875
30 7900 JAMES 950
30 7499 ALLEN 1600
30 7521 WARD 1250
30 7654 MARTIN 1250
30 7698 BLAKE 2850
30 7844 TURNER 1500
小计 9400
合计 29025