ITPub博客

首页 > Linux操作系统 > Linux操作系统 > rollup与cube分析函数学习总结

rollup与cube分析函数学习总结

原创 Linux操作系统 作者:mafeicnnui 时间:2011-08-16 15:34:18 0 删除 编辑

cube: 有2的n次方种组合:

select empno,ename,job,
       sum(sal),
       grouping(empno) gempno,
       grouping(ename) gename,
       grouping(job) gjob
 from scott.emp group by cube(empno,ename,job)
 order by gempno,gename,gjob;

等效于:以上SQL总共有8种组合

empno,ename,job
empno,ename,null
empno,null,job
empno,null,null
null,ename,job
null,ename,null
null,null,job
null,null,null
 

select empno,ename,job,0 gempno,0 gename,0 gjob, sum(sal) from scott.emp group by empno,ename,job
union all
select empno,ename,null,0 gempno,0 gename,1 gjob ,sum(sal) from scott.emp group by empno,ename
union all
select empno,null,job,0 gempno,1 gename,0 gjob ,sum(sal) from scott.emp group by empno,job
union all
select empno,null,null,0 gempno,1 gename,1 gjob,sum(sal) from scott.emp group by empno
union all
select null,ename,job,1 gempno,0 gename,0 gjob,sum(sal) from scott.emp group by ename,job
union all
select null,ename,null,1 gempno,0 gename,1 gjob,sum(sal) from scott.emp group by ename
union all
select null,null,job,1 gempno,1 gename,0 gjob,sum(sal) from scott.emp group by job
union all
select null,null,null,1 gempno,1 gename,1 gjob,sum(sal) from scott.emp
order by gempno,gename,gjob;

rollup:有2*n种组合,从左到右的方法
select empno,ename,job,
       sum(sal),
       grouping(empno) gempno,
       grouping(ename) gename,
       grouping(job) gjob
 from scott.emp group by rollup(empno,ename,job)
 order by gempno,gename,gjob;

等效于:以上SQL总共有4种组合
empno,ename,job
empno,ename,null
empno,null,null
null,null,null

select empno,ename,job,0 gempno,0 gename,0 gjob, sum(sal) from scott.emp group by empno,ename,job
union all
select empno,ename,null,0 gempno,0 gename,1 gjob ,sum(sal) from scott.emp group by empno,ename
union all
select empno,null,null,0 gempno,1 gename,1 gjob,sum(sal) from scott.emp group by empno
union all
select null,null,null,1 gempno,1 gename,1 gjob,sum(sal) from scott.emp
order by gempno,gename,gjob;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8319781/viewspace-704991/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-12

  • 博文量
    7
  • 访问量
    4950