Oracle高级查询之GROUP BY

开篇一笑：两个男人在饭店里边吃饭边聊天，甲：“我不得不在这儿吃饭，因为我妻子不想做饭。”乙：“您真幸运，我之所在这吃饭，是因为我妻子一定要做饭。”

 

 

为了方便大家学习和测试，所有的例子都是在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。