Oracle分组函数以及数据分组

简单总结一下对于数据的分组和分组函数。

本文所举实例，数据来源oracle用户scott下的emp,dept ,salgrade 3表：数据如下：

 

 

 一、分组函数

 1、sum()求和函数、max()求最大值函数、min()求最小值函数、avg()求平均值函数、count()求总行数函数   

Expression:   sum(column)、max(cloumn)、min(cloumn)、avg(column)、count(column)   其中column都是字段名称

Example:

select avg(sal),max(sal),min(sal),sum(sal),count(sal) from emp;

 

 2、distinct关键字：英译有区别的，用于对同一个列去除重复值： Expression:      dinsticnt(column)

Example:

select distinct deptno from emp;

3、wm_concat函数：行转列函数，将列值以逗号作为分割显示在一行的函数。    Expression(column)

Example:

select wm_concat(ename) from emp;

二、数据分组

1、group by分组子句：和分组函数结合使用，对其他条件进行分组，如求每个部门的平均工资，平均工资用分组函数avg，每个部门则需用group by 分组

Example:

select deptno,avg(sal) from emp group by deptno;

注意：group by字句中的条件必须是所有的除了分组以外的所有列才不会出错，并且必须和分组函数结合使用否则没有分组的意义，并且会报错。

2、having 子句：如果查询条件中有分组函数，此时不能使用where子句，替换为having子句，用于对分组后的结果进行过滤。

Example:

 

可以看出where子句不允许使用分组函数

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

 三、经典实例分析：

1、求部门平均薪水的等级和求部门平均的薪水等级。

这里多用到一个薪水等级的表salgrade：

select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) a join salgrade on
(a.avg_sal between losal and hisal);

解析：第一问：由问题可知需要求三个值，deptno,avg_sa,grade,求解的是平均薪水的等级，那么先求部门的平均薪水，用分组函数和group_by子句，分组条件很明显deptno,将此结果看做一个新表a，其中可以查询到deptno和avg_sal，接下来还有一个等级需要求解，直接join表salgrade，条件也很明显，平均工资在低工资和高工资之间就可以了，加入条件后即可查询出对应的grade等级

select deptno,avg(grade) from (select deptno,grade from emp e join salgrade on e.sal between losal and hisal) group by deptno; 

第二问：由题意分析出需要求解2个值deptno,avg(grade),平均 的薪水等级重心是等级，划分为小问题先求解每个员工薪水等级，从emp表联合salgrade表查询，条件是每个员工工资在losal和hisal之间，这样就求出了部门号和每个员工的工资等级，那么再求平均等级只需要avg函数，又题目求得是每个部门的工资等级，只需要分组即可，分组条件deptno。

注：此类题型注重对问题的解读，分析出求解几个值，再使用传统思路将大问题划分为若干小问题，由小及大解决即可。

 

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2018-08-10  09:27:27