Oracle分组函数之ROLLUP、GROUPING用法_oracle rollup grouping

一、简要介绍

1. rollup用于分组统计，也属于oracle分析函数的一种，同理：grouping也是；
2. 假设有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);
1
2
3
4
5
6
7
8
9
10
11
12
13

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)
1
2
3
4

 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16