开篇一笑:两个男人在饭店里边吃饭边聊天,甲:“我不得不在这儿吃饭,因为我妻子不想做饭。”乙:“您真幸运,我之所在这吃饭,是因为我妻子一定要做饭。”
为了方便大家学习和测试,所有的例子都是在Oracle自带用户Scott下建立的。
现在客户的需求是统计部门中每种工作的工资总额,最后还需要统计所有人的工资总数,相信这样的需求对大家来说还是比较简单的,很快就能写出SQL语句,如下:
- select deptno, job, sum(sal)
- from scott.emp
- group by deptno, job
- order by deptno
- union all
- select null deptno, null job, sum(sal) from scott.emp;
客户拍了一下脑袋瓜(当成西瓜拍了),再统计每个部门的工资数吧。tnnd,我加一个union all搞定,修改后的SQL语句如下:
- select deptno, job, sum(sal)
- from scott.emp
- group by deptno, job
- union all
- select deptno, null job, sum(sal)
- from scott.emp
- group by deptno
- union all
- select null deptno, null job, sum(sal) from scott.emp;
老师批注:为什么要写的这么啰嗦呢?其实有更简介的语句可以实现同样的功能,看好了:
- select deptno, job, sum(sal) from scott.emp group by rollup(deptno, job);
客户的需求就好像肾虚的人尿频一样(谁说客户是上帝,上帝哪来那么多的需求?),再统计每个工作类型的工资总额吧。tnnd,我再加一个union all再搞定,修改后的SQL语句如下:
- select deptno, job, sum(sal)
- from scott.emp
- group by deptno, job
- union all
- select deptno, null job, sum(sal)
- from scott.emp
- group by deptno
- union all
- select null deptno, job, sum(sal)
- from scott.emp
- group by job
- union all
- select null deptno, null job, sum(sal) from scott.emp;
老师再批注:老师在讲你有没有在听,根本就没有在听嘛!再教你一个简洁的SQL语句,请看:
- select grouping(job),deptno, job, sum(sal)
- from scott.emp
- group by cube(deptno, job)
- order by deptno;
客户想了想说,只要统计部门工资总额和工作类型工资总额就可以了(我每天都徘徊在杀人和忍住不杀之间),我fucking减掉一个union all搞定,修改后的SQL语句如下:
- select null deptno, job, sum(sal)
- from scott.emp
- group by job
- union all
- select deptno, null job, sum(sal) from scott.emp group by deptno;
老师再再批注:你小子算是彻底没救了,但我是老师啊,决不放弃任何一个学生。再再教你最后一招,仔细看下面:
- select grouping(deptno),grouping(job),grouping_id(job), deptno, job, sum(sal)
- from scott.emp
- group by grouping sets(deptno, job);
忙活半天总算把客户的需求都满足了,稍微松了一口气,不过既然学到新东西,我们有必要最后总结一下。
GROUP BY ROLLUP(A,B,C):首先对(A,B,C)进行GROUP BY,然后对(A,B)进行GROUP BY,然后是(A)进行GROUP BY, 最后对全表进行GROUP BY操作。
GROUP BY CUBE(A,B,C):首先对(A,B,C)进行GROUP BY,然后依次对(A,B)、(A,C)、(A)、(B,C)、(B)、(C)进行GROUP BY,最后对全表进行GROUP BY操作。
GROUP BY GROUPING SETS(A,B,C):依次对(C)、(B)、(A)进行GROUP BY。