Oracle数据库之SQL之Group by总结

Oracle数据库之SQL之Group by总结

前言  
  前面提到了分组函数，其实作用于一组记录的，那么这一组记录可由什么产生呢？不可能
  都是以整张表的形式吧。这就需要Group by子句来完成了。
 Group by
  Group by子句：
  ●  Group by子句将一个表分成许多小组，并对每一个小组返回一个计算值。
  ●  Group by expression：指按什么列进行分组
  注意事项： 
  ●  在select子句中，如果使用了分组函数，就不能对group by指定的列使用分组函数。
  ●  使用where子句可以预先排除某些记录
  ●  在Group by子句中必须有表中的列
  ●  Group by子句不能使用别名
  ●  可以通过Order by子句改变它的排序情况
 基本用法
  对于其基本的用法直接以实例的形式来展示。
  1、统计各个部门的员工的工资的总和

[sql] 

select deptno ,sum(sal) from emp group by deptno; 
--升序排列 
select deptno ,sum(sal) from emp group by deptno order by deptno asc; 

  2、统计各个部门各个职业的员工的工资的总和 

[sql] 

select deptno,job,sum(sal) from emp 
 group by(deptno,job) order by deptno; 

  Tips：第二个实例其实就是多列分组，先对部门进行分组，之后对职位进行分组。

 
  3、Having子句的使用
  我们知道分组函数是不能卸载WHERE子句中的，但是有时候我们需要对分组进行限定
  只有符合某个要求的分组才会被选择出来，那么就可以通过having子句来进行。具体的用法
  同样以一个实例说明。
  在2的基础上添加一个条件：工资总和必须在10000以上

[sql] 

select deptno ,sum(sal) from emp group by deptno 
having sum(sal)>10000; 

 
 Tips：思考一下Having子句与where子句的区别

  两者都是对数据进行筛选，不同的是where是对原数据进行筛选而having则是对汇总
  后的结果进行一个筛选而已！
 扩展用法
  除了基本的用法外，group by还具有一些扩展的用法，不过大多数情况下基本的用法基本上
  就可以满足我们的操作了。
 1、使用rollup操作符
  rollup，是group by子句的一种扩展，可以为每个分组返回小计记录以及对所有的分组返回
  总计记录。下面看看其基本的用法吧。
  ⊙  向rollup传递一列

[sql] 

select deptno,sum(sal) from emp where DEPTNO>=20 group by rollup(deptno) ; 

 

  不过需要注意的是要对所有的记录进行一个总计的话，应该要一个聚合函数
  不然根本没有实际的意义！
 ⊙  向rollup传递多列
  Tips：需要注意的是rollup作用于多列的时候,之对第一列起作用！

[sql] 

<span style="color:#000000;"> select deptno,job,sum(sal) from emp group by rollup(deptno,job); 
 select deptno,job,sum(sal) from emp group by rollup(job,deptno);</span> 

 
  可以看出的是除了最后又一个总计外，每个deptno都有一个小计，至于两个

  列交换的结果原理是一样的，这里就不在演示了。
 
  2、使用cube操作符

  cube也是Group by子句的一种扩展，返回每一个列组合的小计记录，同时在头部加上
  总计记录。（Oracle 11g）貌似和以前不一样？
  ⊙  向cube传递一列

[sql] 

select deptno,sum(sal) from emp group by cube(deptno); 

 

  效果看起来和rollup没有什么两样嘛，只是总计的位置变了嘛，别慌看多列的情况！
 ⊙  向cube传递多列 

[sql] 

select deptno,job,sum(sal) from emp group by cube(deptno,job); 

 
  可以看出cube在每一个deptno都返回一个记录（部门的所有工资总数），并且就部门中的

  每种工作的工资总数做了一个小计，而且就每种工作的工资做了一个小计(没有部门限制)，而且
  对所有的工资总数做了一个总计。
  cube和rollup的区别小结：
  通过上面的学习，我们可能有一种感觉，那就是直观上cube和rollup的组合情况不一样，
  可以从这方面去区分，但是最好别从这方面思考问题。
  ROLLUP (a,b,c...n) 
  group by (a,b,c)->(a,b)->(a)->全表group by  共分组n+1次
  CUBE(a,b,c...n)
  group by(a,b,c)->(a,b)->(a,c)->(a)->(b,c)-(b)-(c)->全表group by  共分组2n次方
  至于其他的扩展用法，就先不展示了，之后会遇到的！Over！