grouping sets
group by grouping sets(a,b,c) = group by a
union all group by b
union all group by c
SQL> list
1 select to_char(hiredate,'yyyy'),deptno,job,sum(sal)
2 from emp
3* group by grouping sets(to_char(hiredate,'yyyy'),deptno,job)
SQL> /
TO_CHAR(HIRE DEPTNO JOB SUM(SAL)
------------ ---------- --------------------------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
30 9400
20 10875
10 8750
1987 4100
1980 800
1982 1300
1981 22825
12 rows selected.
将to_char(hiredate,'yyyy')移到group中,
对于每个入职年份, 每个部门+每个职位的统计 和 每个职位+每个部门 的统计。
SQL> edit
Wrote file afiedt.buf
1 select to_char(hiredate,'yyyy'),deptno,job,sum(sal)
2 from emp
3 group by to_char(hiredate,'yyyy'),grouping sets(deptno,job)
4* order by 1
SQL> /
TO_CHAR(HIRE DEPTNO JOB SUM(SAL)
------------ ---------- --------------------------- ----------
1980 20 800
1980 CLERK 800
1981 SALESMAN 5600
1981 ANALYST 3000
1981 MANAGER 8275
1981 30 9400
1981 PRESIDENT 5000
1981 CLERK 950
1981 20 5975
1981 10 7450
1982 10 1300
1982 CLERK 1300
1987 ANALYST 3000
1987 20 4100
1987 CLERK 1100
15 rows selected.
grouping sets中加入rollup
SQL> edit
Wrote file afiedt.buf
1 select deptno,job,sum(sal)
2 from emp
3* group by grouping sets(rollup(deptno),rollup(job))
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------------------------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
10 8750
20 10875
30 9400
29025
29025
10 rows selected.
rollup,cube不能接受grouping sets作为参数,rollup和cube之间也不能相互作为参数。
group by grouping sets(a,b,c) = group by a
union all group by b
union all group by c
SQL> list
1 select to_char(hiredate,'yyyy'),deptno,job,sum(sal)
2 from emp
3* group by grouping sets(to_char(hiredate,'yyyy'),deptno,job)
SQL> /
TO_CHAR(HIRE DEPTNO JOB SUM(SAL)
------------ ---------- --------------------------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
30 9400
20 10875
10 8750
1987 4100
1980 800
1982 1300
1981 22825
12 rows selected.
将to_char(hiredate,'yyyy')移到group中,
对于每个入职年份, 每个部门+每个职位的统计 和 每个职位+每个部门 的统计。
SQL> edit
Wrote file afiedt.buf
1 select to_char(hiredate,'yyyy'),deptno,job,sum(sal)
2 from emp
3 group by to_char(hiredate,'yyyy'),grouping sets(deptno,job)
4* order by 1
SQL> /
TO_CHAR(HIRE DEPTNO JOB SUM(SAL)
------------ ---------- --------------------------- ----------
1980 20 800
1980 CLERK 800
1981 SALESMAN 5600
1981 ANALYST 3000
1981 MANAGER 8275
1981 30 9400
1981 PRESIDENT 5000
1981 CLERK 950
1981 20 5975
1981 10 7450
1982 10 1300
1982 CLERK 1300
1987 ANALYST 3000
1987 20 4100
1987 CLERK 1100
15 rows selected.
grouping sets中加入rollup
SQL> edit
Wrote file afiedt.buf
1 select deptno,job,sum(sal)
2 from emp
3* group by grouping sets(rollup(deptno),rollup(job))
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------------------------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
10 8750
20 10875
30 9400
29025
29025
10 rows selected.
rollup,cube不能接受grouping sets作为参数,rollup和cube之间也不能相互作为参数。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24237320/viewspace-2081212/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24237320/viewspace-2081212/