Enhancements to the Group By clause:

The ROLLUP operator creates subtotals that roll up from the most detailed level to a grand total, following
the grouping list specified in the GROUP BY clause
The Rollup operation to produce cumulative aggregates such as subtotals:
SQL> select deptno,job,sum(sal) from emp
2 where deptno<40
3 group by deptno,job;

DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 5385
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

已选择9行。

SQL> select deptno,job,sum(sal) from emp
2 where deptno<40
3 group by rollup(deptno,job);

DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 5385
20 13285
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
31435

已选择13行。
Then it creates progressively higher-level subtotals, moving from right to left through the
list of grouping columns. (In the preceding example, the sum of salaries for each department is calculated,
followed by the sum of salaries for all departments.):

SQL> select deptno,job,sum(sal) from emp
2 where deptno<40
3 group by rollup(job,deptno);

DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
20 CLERK 1900
30 CLERK 950
CLERK 4150
20 ANALYST 6000
ANALYST 6000
10 MANAGER 2450
20 MANAGER 5385
30 MANAGER 2850
MANAGER 10685
30 SALESMAN 5600

DEPTNO JOB SUM(SAL)
---------- --------- ----------
SALESMAN 5600
10 PRESIDENT 5000
PRESIDENT 5000
31435

已选择15行。

While ROLLUP produces only a
fraction of possible subtotal combinations, CUBE produces subtotals for all possible combinations of
groupings specified in the GROUP BY clause, and a grand total.

SQL> select deptno,job,sum(sal) from emp
2 where deptno<40
3 group by cube(deptno,job);

DEPTNO JOB SUM(SAL)
---------- --------- ----------
31435
CLERK 4150
ANALYST 6000
MANAGER 10685
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 13285

DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 5385
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

已选择18行。
可以明显看到cube首先按一个部门分组,求针对每个job的sum(sal)；其次按deptno分组求那些部门的sum(sal)；再次按每一种job来求total

sum(sal);最后按所有部门求total sum(sal)。
（CLERK 4150）this rows is cross-tabulation-rows.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8382469/viewspace-259840/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8382469/viewspace-259840/