分组统计查询

—————分组查询——————

分组统计查询往往指的是部分数据具备某些共性
⑤控制要显示的数据列 SELECT[DISTINCT]*|列名称[别名]
①确定数据来源 FROM 表名称[别名]
②确定满足条件的数据行 [WHERE 过滤条件（s）]
③针对数据实现分组 GROUP BY 分组字段，分组字段
④针对于分组后的数据进行筛选，HAVING 分组后的过滤条件
⑥针对数据结构进行排序 ORDER BY 字段[ASC|DESC],…
例：按照职业分组，统计出每个职位的名称、平均人数。平均工资
SELECT job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY job;
例：查询出每个部门标号，以及每个部门人数、最高最低工资。
SELECT deptno,COUNT(empno),MAX(sal),MIN(sal)
FROM emp
GROUP BY deptno;
job 和 emp 都存在重复数据
限制1：在没有编写GROUP BY 字句的时候（全表作为一组）,SELECT字句之中只允许
出现统计函数，不允许出现任何的其他字段：
- 错误的代码
SELCET COUNT（emono）,ename FROM emp;
- 正确的代码
SELCET COUNT（emono）FROM emp；
限制2：在使用GROUP BY 字句分组时，SELECT字句之中只允许出现分组字段与统计函数，其他字段不允许出现
- 正确的代码
SELECT job,COUNT(empno) FROM emp GROUP BY job;//job都是分组字段
- 错误的代码
SELECT job,ename，COUNT(empno) FROM emp GROUP BY job;
限制3：统计函数允许嵌套查询，但是嵌套后的统计查询中，SELECT查询中不允许出现任何字段，包括分组字段，只能使用嵌套的统计函数
- 正确的代码
SELECT MAX(AVG(sal))
FROM emp
GROUP BY deptno;
- 错误的代码
SELECT deptno,MAX(AVG(sal))
FROM emp
GROUP BY deptno;//错误原因不是单组分组函数

分组多表查询

例：查询每个部门的名称、人数、平均工资
确定使用的数据表
-dept表：部门名称
-emp表：统计出人数、平均工资
确定已知关联字段
-雇员与部门：emp.deptno = dept.deptno。
1.先查询每个雇员的编号、部门名称、工资
SELECT e.empno,d.dname,e.sal
FROM emp e,dept d
WHERE e.deptno = d.deptno;
2.dname字段上出现了重复数据，有重复数据才可以分组，根据部门名称分组，直接对查询结果分组
SELECT d.dname,count(e.empno),AVG(sal)
FROM emp e,dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;
3.部门一共有四个，但是现在只出现了三个，加入外连接控制
SELECT d.dname,count(e.empno),AVG(sal)
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno
GROUP BY d.dname;
例：查询每个部门的编号、名称、位置、部门人数、平均服务年限
确定使用的数据表
-dept表：部门编号、名称、位置
-emp表：部门人数、平均服务年限
确定已知关联字段
-雇员与部门：emp.deptno = dept.deptno。
1.先查询每个雇员的编号、名称、位置、雇员编号、雇用日期
SELECT d.deptno,d.dname,d.loc,e.empno,e.hiredate
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno;
2.通过以上查询可以发现，此时三个字段（deptno、dname、loc）上都有重复数据
可以使用多字段分组操作（只有多个列的数据完全重复的时候才使用）
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(MONTHS_BETWEEN(sysdate,e.hiredate)/12) year
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno
GROUP BY d.deptno,d.dname,d.loc;//只有GROUP BY 里面出现的东西SELECT里面才能出现
例：要求查询出平均工资高于2000的职位名称以及平均工资
SELECT job,AVG(sal)
FROM emp
WHERE AVG(sal)>2000
GROUP BY job;//错误原因：此处不允许使用分组函数 WHERE字句上不允许出现分组函数
因为统计统计之后属于GROUP BY之后的范畴，而WHERE是在GROUP BY操作之前使用的
SELECT job,AVG(sal)
FROM emp
HAVING AVG(sal)>2000
GROUP BY job;

WHERE与Having的区别

例：统计所有非销售人员工作名称及从事同一工作雇员的月工资的总和，并且满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资的合计升序排列
1.显示所有非销售人员
SELECT *
FROM emp
WHERE job<>’SALESMAN’;
2.按照工作分组，统计所有的工资总和
SELECT job,SUM(sal)
FROM emp
WHERE job<>’SALESMAN’
GROUP BY job;
3.工资大于5000才进行升序显示
SELECT job,SUM(sal) sum
FROM emp
WHERE job<>’SALESMAN’
GROUP BY job
HAVING SUM(sal)>5000
ORDER BY sum ASC;
例：统计公司所有领取佣金与不领取佣金的雇员人数、平均工资
SELECT comm,COUNT(empno),AVG(sal)
FROM emp
GROUP BY comm;//自身进行分组统计，错误
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;
查询1:所有领取佣金的雇员人数、平均工资；
SELECT ‘领取佣金’title,COUNT(empno),AVG(sal)FROM emp WHERE comm IS NOT NULL;
查询2：查询所有不领取佣金的雇员人数、平均工资；
SELECT ‘不领取佣金’title,COUNT(empno),AVG(sal)FROM emp WHERE comm IS NULL;

总结

分组统计前先看重复列，如果有多个就写多个字段
使用限制：
分组函数嵌套后不能出现任何字段
分组函数可以单独使用，如果使用了GROUP BY才可以在SELECT当中使用
多表查询与分组统计的时候，查询结果相当于一张临时表，所有分组在临时表当中使用