Oralce高级sql之rollup与cube - 数据库编程

ME JOB TO_C SUM(SAL) -------------- --------- ---- ---------- 29025 1980 800 1981 22825 1982 1300 1987 4100 CLERK 4150 CLERK 1980 800 CLERK 1981 950 CLERK 1982 1300 CLERK 1987 1100 ANALYST 6000 ANALYST 1981 3000 ANALYST 1987 3000 MANAGER 8275 MANAGER 1981 8275 SALESMAN 5600 SALESMAN 1981 5600 PRESIDENT 5000 PRESIDENT 1981 5000 SALES 9400 SALES 1981 9400 SALES CLERK 950 SALES CLERK 1981 950 SALES MANAGER 2850 SALES MANAGER 1981 2850 SALES SALESMAN 5600 SALES SALESMAN 1981 5600 RESEARCH 10875 RESEARCH 1980 800 RESEARCH 1981 5975 RESEARCH 1987 4100 RESEARCH CLERK 1900 RESEARCH CLERK 1980 800 RESEARCH CLERK 1987 1100 RESEARCH ANALYST 6000 RESEARCH ANALYST 1981 3000 RESEARCH ANALYST 1987 3000 RESEARCH MANAGER 2975 RESEARCH MANAGER 1981 2975 ACCOUNTING 8750 ACCOUNTING 1981 7450 ACCOUNTING 1982 1300 ACCOUNTING CLERK 1300 ACCOUNTING CLERK 1982 1300 ACCOUNTING MANAGER 2450 ACCOUNTING MANAGER 1981 2450 ACCOUNTING PRESIDENT 5000 ACCOUNTING PRESIDENT 1981 5000 48 rows selected.
2.3 部分 cube (partial cube)
部分 cube 类似于部分 rollup, 把列放在 cube 操作符的外面能够限制生成列组合的小计. 例如, group by a, cube(b, c), 这条语句将产生 4 (2 * 2) 个层次的小计, 分别为层次 (a, b, c), 层次 (a, b), 层次 (a, c), 层次 (a).

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)
  2  from emp a, dept b
  3  where a.deptno = b.deptno
  4  group by b.dname, cube(a.job, to_char(hiredate, 'yyyy'));

DNAME          JOB       TO_C   SUM(SAL)
-------------- --------- ---- ----------
SALES                               9400
SALES                    1981       9400
SALES          CLERK                 950
SALES          CLERK     1981        950
SALES          MANAGER              2850
SALES          MANAGER   1981       2850
SALES          SALESMAN             5600
SALES          SALESMAN  1981       5600
RESEARCH                           10875
RESEARCH                 1980        800
RESEARCH                 1981       5975
RESEARCH                 1987       4100
RESEARCH       CLERK                1900
RESEARCH       CLERK     1980        800
RESEARCH       CLERK     1987       1100
RESEARCH       ANALYST              6000
RESEARCH       ANALYST   1981       3000
RESEARCH       ANALYST   1987       3000
RESEARCH       MANAGER              2975
RESEARCH       MANAGER   1981       2975
ACCOUNTING                          8750
ACCOUNTING               1981       7450
ACCOUNTING               1982       1300
ACCOUNTING     CLERK                1300
ACCOUNTING     CLERK     1982       1300
ACCOUNTING     MANAGER              2450
ACCOUNTING     MANAGER   1981       2450
ACCOUNTING     PRESIDENT            5000
ACCOUNTING     PRESIDENT 1981       5000

29 rows selected.

Oralce高级sql之rollup与cube(三)