/******************
*ROLLUP函数的使用
******************/
案例1
需求:
× 统计每个部门每个职位的薪水和
× 统计每个部门所有职位的薪水小计
× 统计所有部门所有职位的薪水合计
× 需要显示部门名、职位名和累加后的薪水
--需求1
SELECT D.DNAME, E.JOB, SUM(E.SAL) SUM_SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO
GROUP BY D.DNAME, E.JOB
UNION ALL
--需求2
SELECT D.DNAME, NULL, SUM(E.SAL) SUM_SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO
GROUP BY D.DNAME
UNION ALL
--需求3
SELECT NULL, NULL, SUM(E.SAL) SUM_SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO;
--使用ROLLUP分组
SELECT D.DNAME, E.JOB, SUM(E.SAL) AS SUM_SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO
GROUP BY ROLLUP(D.DNAME, E.JOB);
ROLLUP(D.DNAME, E.JOB)的分组过程是:
1)标准分组:GROUP BY (D.DNAME, E.JOB),对每个部门每个职位进行分组
2)从右到左递减:GROUP BY (D.DNAME, NULL),其实这个NULL没有必要使用,这里只是为了
方便分析。这个过程是对上一个级别分组的小计,也就是对每个DNAME值,计算横跨所有
JOB的小计。
3)最后合计:相当于 GROUP BY (NULL,NULL).
上面的ROLLUP只用了两个列,如果有N个列,那么结果就是n+1中group by的组合,
从右到左递减的过程中,下一个分组就是对上一个分组的小计。
另外,在ROLLUP操作是,如果使用HINT:expand_gset_to_union,则优化器会将ROLLUP转为
对应的UNION ALL操作。
案例2
需求
× 计算每个入职时间(年)、部门、职位的标准分组的薪水和;
× 计算每个入职时间(年)、部门的所有职位的薪水小计;
× 计算每个入职时间(年)的所有部门所有职位的薪水小计。
× 最后合计薪水,显示入职时间(年)、部门名、职位名
SELECT TO_CHAR(E.HIREDATE, ' yyyy') HIRE_YEAR,
D.DNAME,
E.JOB,
SUM(SAL) SUM_SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO
GROUP BY ROLLUP(TO_CHAR(E.HIREDATE, ' yyyy'), D.DNAME, E.JOB)