ROLLUP在GROUP BY中的使用
SELECT department_id, job_id,SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);
DEPTNO JOB AVG(SAL)
------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 2916.66667
20 CLERK 950
20 ANALYST 3000
20 MANAGER 2975
20 1968.75
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
30 1566.66667
40
40
50
50 ANALYST 3000
50 3000
2073.21429
结果:除了分组外,还加上按DEPTNO的小计
cube在GROUP BY中的使用
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------- ----------
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 7875
20 CLERK 1900
20 ANALYST 3000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
40
40
50
50 3000
50 ANALYST 3000
24 rows selected.
SQL> ;
1 select deptno,job,sum(sal)
2* from emp group by cube(deptno,job)
SQL>
结果:除了分组外,按DEPTNO的小计,并按job进行sum(sal),显示在行集前面.
Grouping 结合cube或rollup在GROUP BY中的使用
SQL> ;
1 select deptno,job,sum(sal),
2 grouping(deptno) grp_dept,grouping(job) grp_job
3* from emp where deptno<60 group by cube(deptno,job)
SQL> /
DEPTNO JOB SUM(SAL) GRP_DEPT GRP_JOB
---------- --------- ---------- ---------- ----------
1 0
29025 1 1
CLERK 4150 1 0
ANALYST 6000 1 0
MANAGER 8275 1 0
SALESMAN 5600 1 0
PRESIDENT 5000 1 0
10 8750 0 1
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
20 7875 0 1
20 CLERK 1900 0 0
20 ANALYST 3000 0 0
20 MANAGER 2975 0 0
30 9400 0 1
30 CLERK 950 0 0
30 MANAGER 2850 0 0
30 SALESMAN 5600 0 0
40 0 0
40 0 1
50 0 0
50 3000 0 1
50 ANALYST 3000 0 0
24 rows selected.
Grouping sets 代替cube或rollup在GROUP BY中的使用
SQL> select deptno,job,mgr,sum(sal)
2 from emp group by grouping sets
3 ((deptno,job),(job,mgr));
DEPTNO JOB MGR SUM(SAL)
---------- --------- ---------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 3000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
40
50
50 ANALYST 3000
CLERK 7698 950
CLERK 7782 1300
CLERK 7788 1100
CLERK 7902 800
ANALYST 7566 6000
MANAGER 7839 8275
SALESMAN 7698 5600
PRESIDENT 5000
21 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/271063/viewspace-909523/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/271063/viewspace-909523/