GROUP函数

 

 

select group_id() gid, e.deptno, count(*)
  from emp e, salgrade s
 where sal between losal and hisal
 group by cube(e.deptno, e.deptno)
 order by deptno, gid;

 

 

select grouping(grade) gi_g,
       grouping(e.deptno) gi_d,
       grouping_id(grade) gid_g,
       grouping_id(e.deptno) gid_d,
       grouping_id(grade, e.deptno) gid_gd,
       grouping_id(e.deptno, grade) gid_dg,
       e.deptno,
       grade,
       count(*)
  from emp e, salgrade s
 where sal between losal and hisal
 group by cube(e.deptno, grade)
 order by deptno, grade;

 

 分组求和后的行列转换

根据业务需求，利用 rollup、cube或 grouping sets,计算出所需的分组求和数值
以此中间结果为基础，运用统计进行行列转换实现业务报表需求
在11g中可以使用 pivot来做行列转换

with t as
 (select grouping_id(e.deptno, grade) gid_dg, e.deptno, grade, count(*) cnt
    from emp e, salgrade s
   where sal between losal and hisal
   group by cube(e.deptno, grade)
   order by deptno, grade),
u as
 (select decode(gid_dg, 2, 99, 3, 99, deptno) deptno,
         decode(gid_dg, 1, cnt, 3, cnt) subtotal,
         decode(grade, 1, cnt) sg1,
         decode(grade, 2, cnt) sg2,
         decode(grade, 3, cnt) sg3,
         decode(grade, 4, cnt) sg4,
         decode(grade, 5, cnt) sg5
    from t)
select deptno,
       max(subtotal) subtotal,
       max(sg1) sg1,
       max(sg2) sg2,
       max(sg3) sg3,
       max(sg4) sg4,
       max(sg5) sg5
  from u
 group by deptno
 order by deptno;

 

 pivot

with t as
 (select grouping_id(e.deptno, grade) gid_dg, e.deptno, grade, count(*) cnt
    from emp e, salgrade s
   where sal between losal and hisal
   group by cube(e.deptno, grade)
   order by deptno, grade),
u as
 (select decode(gid_dg, 2, 99, 3, 99, deptno) deptno,
         decode(gid_dg, 1, 9, 3, 9, grade) grade,
         cnt
    from t)
select *
  from (select * from u)
pivot (sum(cnt) cnt for grade in(9, 1, 2, 3, 4, 5))
 order by deptno;