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);
           

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)