一、简要介绍
- rollup用于分组统计,也属于oracle分析函数的一种,同理:grouping也是;
- 假设有n个维度,那么rollup会有n个聚合,cube会有2n个聚合
rollup统计列
rollup(a,b) 统计列包含:(a,b)、(a)、()
rollup(a,b,c) 统计列包含:(a,b,c)、(a,b)、(a)、()
二、实践过程
select a.deptname name, sum(b.sal) total
from dept a, emp b
where a.deptno = b.deptno
group by a.deptname
union all
select null,sum(b.sal)
from dept a, emp b
where a.deptno = b.deptno;
等同于:
select a.deptname, sum(b.sal)
from dept a, emp b
where a.deptno = b.deptno
group by rollup(a.deptname);
select a.deptname name, b.job, sum(b.sal) total
from dept a, emp b
where a.deptno = b.deptno
group by rollup(a.deptname, b.job)
select case grouping(a.deptname)
when 1 then
'总计'
else
a.deptname
end as dname,
case grouping(b.job)
when 1 then
'分计'
else
b.job
end as job,
sum(b.sal) total
from dept a, emp b
where a.deptno = b.deptno
group by rollup(a.deptname, b.job)