Oracle2-Rollup和Cube用法

本文主要是介绍Oracle2-Rollup和Cube用法，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

在Oracle的聚合函数中，会有按照维度统计的情况,比如上图按照job 和 deptno统计 sal的sum

但是也会遇到同时要求统计只按照job维度统计的情况，并且做到一张表里

1 union 来实现维度不一致

首先反应过来的是分两步查询，再讲结果union起来

select job,null as deptno,sum(sal)
from emp
group by jobunion all select job,deptno,sum(sal)
from emp
group by job,deptno

2 rollup 来实现变化维度

2.2 美化一下

SELECTjob,SUM(CASE WHEN deptno = '10' THEN "sum" END) AS "10",SUM(CASE WHEN deptno = '20' THEN "sum" END) AS "20",SUM(CASE WHEN deptno = '30' THEN "sum" END) AS "30",SUM(CASE WHEN deptno = '小计' or deptno = '-' THEN "sum" END) AS "小计"
FROM (SELECT NVL(job, '总计') AS job,CASE WHEN job IS NULL THEN '-' ELSE NVL(TO_CHAR(deptno), '小计') END AS deptno,SUM(sal) AS "sum"FROM empGROUP BY ROLLUP(job, deptno)
) a
GROUP BY job;

3 cube

SELECT job,deptno,SUM(sal) AS "sum"
FROM emp
GROUP BY cube(job, deptno)

4 rollup vs cube

从以上的查询结果可以看到 rollup和 rullup的不同点在于
rollup只会对rollup (A,B) 做变化的维度的聚合

而 cube（A,B）对A和B都做变化维度的聚合

这篇关于Oracle2-Rollup和Cube用法的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---