搞清楚group by、group by rollup和group by cube区别

SQL> select job,deptno,sum(sal) total_sal from scott.emp group by rollup(job,deptno);

JOB DEPTNO TOTAL_SAL
--------- ------ ----------
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
CLERK 4150
ANALYST 20 6000
ANALYST 6000
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
MANAGER 8275
SALESMAN 30 5600
SALESMAN 5600
PRESIDENT 10 5000
PRESIDENT 5000
29025

15 rows selected

group by rollup(job,deptno)从右到左开始计算(job,deptno)->(job)-> (*)

SQL> select job,deptno,sum(sal) total_sal from scott.emp group by job,deptno;

JOB DEPTNO TOTAL_SAL
--------- ------ ----------
MANAGER 20 2975
PRESIDENT 10 5000
CLERK 10 1300
SALESMAN 30 5600
ANALYST 20 6000
MANAGER 30 2850
MANAGER 10 2450
CLERK 30 950
CLERK 20 1900

9 rows selected

group by job,deptno只计算(job,deptno)

SQL> select job,deptno,sum(sal) total_sal from scott.emp group by cube(job,deptno);

JOB DEPTNO TOTAL_SAL
--------- ------ ----------
29025
10 8750
20 10875
30 9400
CLERK 4150
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
ANALYST 6000
ANALYST 20 6000
MANAGER 8275
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
SALESMAN 5600
SALESMAN 30 5600
PRESIDENT 5000
PRESIDENT 10 5000

18 rows selected

group by cube(job,deptno)从右到左开始计算(*)->(deptno)->(job)->(job,deptno)

[@more@]

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

转载于:http://blog.itpub.net/16298743/viewspace-1045785/