group by 扩展函数之GROUPING SETS

/*GROUPING SETS(a,b,c) = GROUP BY a UNION GROUP BY b UNION GROUP BY c
即没有了 GROUP BY NULL*/

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);

-----------

SELECT to_char(b.hiredate,'yyyy') hire_year,NULL,NULL,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY to_char(b.hiredate,'yyyy')
UNION
SELECT NULL,a.dname,NULL,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname
UNION
SELECT NULL,NULL,b.job,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY b.job

------------
/*部分分组 GROUP BY to_char(b.hiredate,'yyyy'),GROUPING sets(a.dname,b.job)与GROUP BY GROUPING sets(to_char(b.hiredate,'