oracle rollup和cube语句分析(二)
JOB SUM_SAL GROUPING(A.DNAME) GROUPING(B.JOB)
---------------------------- ------------------ ---------- ----------------- ---------------
29325 1 1
CLERK 3450 1 0
ANALYST 7000 1 0
MANAGER 8275 1 0
SALESMAN 5600 1 0
PRESIDENT 5000 1 0
SALES 9400 0 1
SALES CLERK 950 0 0
SALES MANAGER 2850 0 0
SALES SALESMAN 5600 0 0
RESEARCH 11175 0 1
RESEARCH CLERK 1200 0 0
RESEARCH ANALYST 7000 0 0
RESEARCH MANAGER 2975 0 0
ACCOUNTING 8750 0 1
ACCOUNTING CLERK 1300 0 0
ACCOUNTING MANAGER 2450 0 0
ACCOUNTING PRESIDENT 5000 0 0
已选择18行。
注释：和rollup的结果相比，cube的所有可能的分组都走一遍。
可以去掉合计和某些不需要的小计，通过部分cube实现。部分cube比部分rollup来得有用多了。
[sql]
21:27:06 scott@ORCL (^ω^) select a.dname,b.job,sum(b.sal) sum_sal,grouping(a.dname),grouping(b.job)
21:32:20 2 from dept a,emp b
21:32:20 3 where a.deptno=b.deptno
21:32:20 4 group by a.dname,cube(b.job)
21:32:21 5 /
DNAME JOB SUM_SAL GROUPING(A.DNAME) GROUPING(B.JOB)
---------------------------- ------------------ ---------- ----------------- ---------------
SALES 9400 0 1
SALES CLERK 950 0 0
SALES MANAGER 2850 0 0
SALES SALESMAN 5600 0 0
RESEARCH 11175 0 1 www.2cto.com
RESEARCH CLERK 1200 0 0
RESEARCH ANALYST 7000 0 0
RESEARCH MANAGER 2975 0 0
ACCOUNTING 8750 0 1
ACCOUNTING CLERK 1300 0 0
ACCOUNTING MANAGER 2450 0 0
ACCOUNTING PRESIDENT 5000 0 0
已选择12行。