分组统计查询
1、预讲知识点
1、基础统计函数的使用
2、分组统计操作的实现，要结合多表查询使用分组统计
3、具体内容
3.1、统计函数
在之前使用过一个COUNT()函数，此函数的功能是统计表中的数据量，那么在SQL之中，定义了5个常用的统计函数：
- 统计个数：COUNT(*|[DISTINCT]字段)、
- MAX(字段)、MIN(字段)、
- SUM(数字字段)、AVG(数字字段)
范例：查询所有雇员之中最高和最低工资
SELECT MAX(sal),MIN(sal) FROM emp;
对于MAX()与MIN()的操作除了可以使用数字之外，也可以使用日期。
范例：查询出所有雇员最早雇佣和最晚雇佣的雇佣日期
SELECT MIN(hiredate),MAX(hiredate) FROM emp;
范例：统计出所有雇员的总工资以及平均工资
SELECT SUM(sal),AVG(sal) FROM emp;
对于SUM()和AVG()函数来说，只要是数值型数据都可以使用。
范例：要求统计出雇员的平均服务年限
SELECT TRUNC(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12)) FROM emp;
面试题：请解释COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)的区别？
- COUNT(*)：明确的返回表中的数据个数，是最准确的；
- COUNT(字段)：不统计为null的数据个数，如果某一列的数据不可能为null，那么结果与COUNT()相同；
- COUNT(DISTINCT 字段)：统计消除掉重复数据后的数据个数
3.2、分组统计查询
能够分组的时候往往指的是部分数据不被某些共性。要想分组，使用GROUP BY子句完成。
SELECT [DISTINCT] 分组字段 [别名],... | 统计函数
FROM 表名称[别名]
[WHERE 过滤条件(s)]
[GROUP BY 分组字段,分组字段,分组字段,...]
[ORDER BY 字段 [ASC|DESC]];
范例：要求按照职位分组，统计出每个职位的名称、人数、平均工资
SELECT job,COUNT(*),AVG(sal)
FROM emp
GROUP BY job;
范例：要求查询出每个部门编号，以及每个部门的人数、最高与最低工资
SELECT deptno,COUNT(*),MAX(sal),MIN(sal)
FROM emp
GROUP BY deptno;
以上实现了分组的操作，但是对于分组操作严格来讲还是存在一些使用上的限制：
●限制一：在没有编写GROUP BY子句的时候（全表做为一组），那么SELECT子句之中只允许出现统计函数，不允许出现任何的其他字段；
| 错误的的代码： | 正确的代码： |
| SELECT COUNT(empno),ename FROM emp; | SELECT COUNT(empno) FROM emp; |
●限制二：在使用GROUP BY子句分组的时候，SELECT子句之中只允许出现分组字段与统计函数，其他字段不允许出现；
| 错误的代码： | 正确的代码： |
| SELECT job,COUNT(*),ename FROM emp GROUP BY job; | SELECT job,COUNT(*) FROM emp GROUP BY job; |
●限制三：统计函数允许嵌套查询，但是嵌套后的统计查询中，SELECT子句里面不允许再出现任何的字段，包括分组字段，只能够使用嵌套的统计函数。
| 错误的代码： | 正确的代码： |
| SELECT deptno,MAX(AVG(sal)) FROM emp GROUP BY deptno; | SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno; |
范例：查询每个部门的名称、人数、平均工资
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.deptno,d.dname,d.loc,COUNT(e.empno),AVG(TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)) YEAR FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc;
多字段分组的时候只有多个列的数据完全重复的时候才可以使用。
范例：要求查询出平均工资高于2000的职位名称以及平均工资。
SQL> SELECT AVG(sal) FROM emp WHERE AVG(sal)>2000 GROUP BY job; SELECT AVG(sal) FROM emp WHERE AVG(sal)>2000 GROUP BY job * ERROR at line 1: ORA-00934: group function is not allowed here
但是运行之后会发现给出的错误提示：WHERE子句上不允许使用分组函数。之所以不能够使用是因为统计的操作属于GROUP BY之后的范畴了，而WHERE是在GROUP BY操作之前使用的，所以此时如果要想针对分组后的数据进行过滤，那么只能够使用HAVING子句。
SELECT [DISTINCT] 分组字段 [别名],... | 统计函数
FROM 表名称[别名]
[WHERE 过滤条件(s)]
[GROUP BY 分组字段,分组字段,分组字段,...]
[HAVING 分组后的过滤条件]
[ORDER BY 字段 [ASC|DESC]];
执行顺序：5，1，2，3，4，6
SELECT job,AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal)>2000;
注意：关于WHERE与HAVING的区别？
●WHERE发生在GROUP BY操作之前，属于分组前的数据筛选，即：从所有的数据之中筛选出可以分组的数据，WHERE子句不允许使用统计函数；
●HAVING发生在GROUP BY操作之后，是针对于分组后的数据进行筛选，HAVING子句可以使用统计函数；
范例：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要求满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列。
SELECT job,SUM(sal) sum
FROM emp
WHERE NOT job=\'SALESMAN\'
GROUP BY job
HAVING SUM(sal)>5000
ORDER BY sum;
思考题：统计公司所有领取佣金与不领取佣金的雇员人数、平均工资
SELECT \'GET COMM\' title,COUNT(empno),AVG(sal) FROM emp WHERE comm IS NOT NULL UNION SELECT \'NO COMM\' title,COUNT(empno),AVG(sal) FROM emp WHERE comm IS NULL;
在整个分组里面一定要注意，只有在基数固定的时候才可能分组。
4、总结
1、不管是单表分组还是多表分组，重点先看重复的列，如果是一个重复列，那么就在GROUP BY后面写一个字段，如果是多个，就写多个字段；
2、分组中的使用限制：
●分组函数嵌套后不能够出现任何字段；
●分组函数可以单独使用，如果使用了GROUP BY还可以在SELECT子句里面出现字段；
3、多表查询与分组统计的时候，查询结果相当于是一张临时表，所有的分组是在临时表里完成的。