Oracle分组统计查询-分组查询

【⑤确定要使用的数据列】SELECT [DISTINCT] 字段 [别名]
【①确定要查询的数据来源】FROM
【②针对数据进行的筛选】WHERE 过滤条件
【③针对数据实现分组】GROUP BY 分组字段，分组字段，…
【④针对分组后的数据进行筛选】HAVING 分组后的过滤条件
【⑥针对返回结果进行排序】ORDER BY 字段 [ASC | DESC]

示例1：要求按照职位分组，统计出每个职位的名称，人数，平均工资
select job,count(empno),avg(sal) from emp group by job;

示例2：要求查询出每个部门编号，以及每个部门的人数，最高与最低工资。
select deptno, count(empno),max(sal),min(sal) from emp group by deptno;

分组操作的使用限制：
限制1：在没有编写group by 时（全表作为一组），select 子句中只允许出现统计函数，不允许出现任何其他字段；
错误语句：select count(empno) , ename from emp;
正确语句：select count(empno) from emp;

限制2：在使用group by 子句分组的时候，select子句之中只允许出现分组字段与统计函数，其他字段不允许出现；
错误语句：select job ,count(empno),ename from emp group by job;
正确语句：select job ,count(empno) from emp group by job;

限制3:统计函数允许嵌套查询，但是嵌套后的统计查询中，select子句里不允许再出现任何的字段，包括分组字段，只能够使用嵌套的统计函数；
错误语句：select deptno,max(avg(sal)) from emp group by deptno;
正确语句：select max(avg(sal)) from emp group by deptno;

以上查询都是针对单表数据的查询，而在分组操作的时候也可以进行多表查询。

示例3：查询每个部门的名称，人数，平均工资；
第一步：
select d.dname, count(e.empno),avg(e.sal)
from emp e, dept d
where e.deptno=d.deptno
group by d.dname;
第二步：部门一共有四个，但第一步只出现三个，需加入外连接控制
select d.dname, count(e.empno),avg(e.sal)
from emp e, dept d
where e.deptno(+)=d.deptno
group by d.dname;

示例4：查询每个部门的编号，名称，位置，部门人数，平均工作年限；

select d.deptno,d..dname, d.loc, count(avg(months_between (sysdate, e.hiredate)/12)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno, d.dname, d.loc;

示例5：查询平均工资高于2000的职位名称以及平均工资；
select job,avg(sal)
from emp
where avg(sal)>2000
group by job;
执行以上语句会出现错误：where子句上不允许使用统计函数，原因是统计操作属于group by 之后的范畴，而where 是在group by 之前使用，所以这种情况使用HAVING 完成。
select job,avg(sal)
from emp
group by job
having avg(sal)>2000;

注意：关于where与having 的区别？
where发生在group by 操作之前，属于分组前的数据筛选，即：从所有的数据之后筛选出可以分组的数据，where子句不允许使用统计函数；
having发生在group by 操作之后，是针对分组后的数据进行筛选，having 子句可以使用统计函数；

综合实例1：显示非销售人员工作名称及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资的合计升序排列。

select job，sum(sal) sum
from emp
where job<>’SALESMAN’
group by job
having sun(sal)>5000
order by sum ASC；

综合实例2：统计公司所有领取佣金与不领取佣金的雇员人数，平均工资。
第一步：
select ‘领取佣金’ title , count(empno),avg(sal)
from emp
where comm is not null;
第二步：
select ‘不领取佣金’ title , count(empno),avg(sal)
from emp
where comm is null;
结果：
select ‘领取佣金’ title , count(empno),avg(sal)
from emp
where comm is not null
union
select ‘不领取佣金’ title , count(empno),avg(sal)
from emp
where comm is null;