在Oracle的聚合函数中，会有按照维度统计的情况,比如上图按照job 和 deptno统计 sal的sum
但是也会遇到同时要求统计只按照job维度统计的情况，并且做到一张表里
1 union 来实现维度不一致
首先反应过来的是分两步查询，再讲结果union起来
select
job,null as deptno,sum(sal)
from emp
group by job
union all
select
job,deptno,sum(sal)
from emp
group by job,deptno
2 rollup 来实现变化维度
2.2 美化一下
SELECT
job
,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 emp
GROUP 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) 做变化的维度的聚合文章来源：https://www.toymoban.com/news/detail-824822.html
而 cube（A,B）对A和B都做变化维度的聚合文章来源地址https://www.toymoban.com/news/detail-824822.html
到了这里，关于Oracle2-Rollup和Cube用法的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
