Oracle：分组函数

多行函数也叫做分组函数，也叫做组函数，作用于一组数据返回一个结果。例如AVG，COUNT，MAX，MIN，SUM。

功能与MySQL一样。

 

--求工资的总额

Select sum(sal) from emp;

--求人数

Select count(*) from emp;

--平均工资，二者结果一样，但是列中有空值，就会有问题。看下面奖金的计算。

Select sum(sal)/count(*) 一,avg(sal) 二 from emp;

 

--平均奖金，奖金列中有null数据。

Select sum(comm)/count(*) 一,sum(comm)/count(comm)二,avg(comm) 三 from emp;

/*

上例中，二和三的结果是一样的，

关于null：组函数有自动滤空的功能，只会统计非空的数。我们可以嵌套虑空函数来屏蔽组函数的虑空功能！

例如：selectcount(*),count(nvl(comm,0)) from emp;使用nvl函数将所有null都换位0，就不存在null了。

此时，count(*)的结果时14，count(comm)的结果是4，因为comm列中有null，count函数是不计算Null的，所以统计结果是4，avg(comm)求comm的平均值，avg也是组函数，不会计算null的值，所以二和三是一样的。

*/

 

GOURP BY：

GOURP BY可以根据gourp by 后面跟的列，把from后面的表中的记录分成若干组，对每一组我们可以使用组函数来求他的某一个值。

--求部门的平均工资，按部门分组，对每组求平均工资。

Select deptno,avg(sal) from emp group bydeptno;

 

/*

将GROUPBY抽象成一个格式：

Select a,组函数(x)from *** group by a;

根据a来分组，

如果是selecta,b,组函数(x),且a和b列都没有出现在后面的组函数中，那么后面的groupby 就必须是a,b，即：select a,b,组函数(x) from *** group by a,b;

如果selecta,b,c,组函数(x)，且a,b和c列都没有出现在后面的组函数中，就是from *** group by a,b,c;

少一列就是错的。语法是错的。

Group by 后面的列可以不出现在select后面的列中，但是select后面的列必须出现在group by 后面的列中。

即：selecta,b,c,组函数(x)from *** group by a,b,c,d;是合法的。

*/

--多个列的分组：按照部门不同的职位统计平均工资：规则是：先按照groupby后面的第一个列来分，如果第一个列

--相同，再按照第二个列来分。

Select deptno,job,avg(sal) from emp groupby deptno,job order by 1;

 

having ：过滤分组，为分组添加条件，类似where

前提：行已经被分组，使用了组函数。

结果：满足having子句中条件的分组将被显示。

--求平均工资大于2000的部门

Select deptno,avg(sal) from emp group bydeptno having avg(sal)>2000;

 

跟where 的区别：

相同点都是过滤分组。

区别是：where后面不能使用组函数。例如上例中，having avg(sal)>2000;使用了avg组函数。没有组函数的时候这两个都可以。

都可以的时候用哪个好呢？见Oracle优化文档第4点。

 

GROUP BY语句的增强：

按照部门统计各部门不同工种的工资情况。要求按如下格式输出：

分成三组：

(1)    观察前三行，是10号部门每个职位的工资总额，可以按照deptno和job一起分组，所以条件是：group by deptno,job

(2)    观察第四行，是10号部门的所有员工工资的总和，根据部门分组，所以条件是：group by deptno

(3)    观察最后一行，是所有员工的工资总额，不用分组，故条件为null，即：group by null

如果将这三个集合加起来就等于：

group by rollup(deptno,job);   这句就叫做group by 语句的增强。注意：这里的deptno,job没有在前面的组函数中，所以rollup中的列要包含deptno,job。

解析rollup:

group by rollup(a,b)

=group by a,b

+group by a

+group by null

Group by 的增强应用于报表的情况比较多。

 

使用select deptno,job,sum(sal) from emp group byrollup(deptno,job);查询结果为：

与给定格式不太一样，相同的部门号只显示一次，且不同部门之间有空行。

可以通过做报表的语句搞定：break on deptno skip 2

然后输入/，执行上一条sql语句，就可以显示需要的格式了。

break on deptno表示相同的部门号只显示一次，skip 2表示不同的部门号之间跳过两行。

如果在设定break on null，则又会还原刚才结果。

 

 

 

 

命令行做报表不方便，可以使用水晶报表。是BO公司的产品，被SAP公司收购了。

软件领域几个比较牛的公司：

IBM；Oracle；微软；谷歌不是软件公司。

SAP：德国的公司，最有名的产品是ERP。