在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) 做变化的维度的聚合
而 cube(A,B)对A和B都做变化维度的聚合