GROUPING SETS进行分组自定义的汇总，以下通过案例详细描述。
测试环境
SQL> drop table dept purge;
SQL> drop table emp purge;
SQL> create table dept as select * from scott.dept;
SQL> create table emp as select * from scott.emp;
SQL> set term off
SQL> set heading on
SQL> set verify off
SQL> set feedback off
SQL> set linesize 2000
SQL> set pagesize 30000
SQL> set long 999999999
SQL> set longchunksize 999999
SQL> set autotrace on explain
GROUPING SETS分组案例
SQL> SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY GROUPING SETS(to_char(b.hiredate,'yyyy'),a.dname,b.job);
以上语句的等价写法
（不但复杂，性能也差）
SQL> SELECT to_char(b.hiredate,'yyyy') hire_year,null as dname,null as job,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY to_char(b.hiredate,'yyyy')
UNION ALL
SELECT null as hire_date,a.dname,null as job,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY null,a.dname,null
UNION ALL
SELECT null as hire_year,null as dname,b.job,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY null,null,b.job;
部分字段GROUPING SETS 分组
SQL> set autotrace off
SQL> SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname, GROUPING SETS(to_char(b.hiredate,'yyyy'),b.job);
CUBE、ROLLUP 作为GROUPING SETS 的参数例子
SQL> SELECT a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY GROUPING SETS(ROLLUP(a.dname),ROLLUP(b.job));
等同的低效写法：
SQL> SELECT a.dname,NULL job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname)
UNION ALL
SELECT NULL dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(b.job);
