分组统计查询

分组统计查询

1.统计函数（分组函数）

常用的统计函数：

• 统计个数：COUNT()；根据表中的实际数据量返回结果
• 求和：SUM()、是针对于数字的统计
• 最小值：MIN()、各种数据类型都支持
• 最大值：MAX()、各种数据类型都支持
• 平均值：AVG()、是针对于数字的统计

范例：验证各个函数

这些统计函数是允许和其他函数嵌套的。

范例：求员工的平均工作年限

范例：求出最早和最晚的雇佣日期

注意：以上几个函数，在表中没有数据的时候，只有COUNT()函数会返回结果，其他都是null。

范例：表中数据为空时，函数返回结果

COUNT函数的三种使用形式：

• COUNT(*)：可以准确的返回表中的全部记录数
• COUNT(字段)：统计不为null的所有数据
• COUNT(DISTINCT 字段)：消除重复数据之后的结果

2.分组统计

什么情况下可能分组？

分组的前提是存在有重复，但是也允许一行记录进行单独分组

如果要进行分组则应该使用GROUP BY子句完成，那么此时的SQL语法格式变为如下形式：

【④选出所需要的数据列】SELECT [DISTINCT] * | 分组列[别名]，分组列 [别名]，分组列 [别名]…

【①确定数据来源】FROM 表名称 [别名]

【②筛选数据行】WHERE 限定条件（s）；

【③针对于筛选的行进行分组】GROUP BY 分组字段、分组字段、分组字段 …;

【⑤数据排序】ORDER BY 排序字段[ASC | DESC] ,排序字段 [ASC | DESC] …;

范例：根据部门编号分组，查询出每个部门的编号、人数、平均工资

范例：根据职位分组，统计出每个职位的人数，最低工资与最高工资

实际上GROUP BY 子句之所以使用麻烦，是因为分组的时候有一些约定条件：

• 如果查询不使用GROUP BY子句，那么SELECT 子句中只允许出现统计函数，其他任何字段不允许出现

  错误代码	正确代码
  SELECT empno，COUNT(*) FROM emp；	SELECT COUNT(*) FROM emp；

• 如果查询中使用了GROUP BY子句，那么SELECT 子句中只允许出现分组字段、统计函数，其他任何字段都不允许出

  现。

  错误代码	正确代码
  SELECT empno， job，COUNT(*) 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;

3.多表查询与分组统计

对于GROUP BY子句而言是在WHERE子句之后执行的，所以使用时可以进行限定查询，也可以进行多表查询。

范例：查询出每个部门的名称、部门人数、平均工资。

• 确定要使用的数据表：

  |-dept表：部门名称

  |-emp表：统计数据

• 确定要使用的关联字段

  |-雇员与部门：emp.deptno=dept.deptno

第一步：换个思路，查询出每个部门的名称、雇员编号（COUNT(empno)）、基本工资AVG（sal）

第二步:此时的查询结果中对于部门名称部分出现了重复的内容，按照分组来讲，只要是出现了数据的重复，那么就可以进行分组，只不过此时的分组是针对于临时表（查询结果）。

第三步：在dept表中实际上存在有四个部门信息，而此时的要求也是统计所有的部门名称，如果发现数据不完整，则需要采用外连接。

范例：查询每个部门的编号、名称、位置、部门人数、平均工资。

• 确定要使用的数据表：

  |-dept表：编号、名称、位置；

  |-emp表：统计信息

• 确定已知的关联字段：

  |-雇员与部门：emp.deptno=dept.deptno

第一步：查询每个部门的编号、名称、位置、雇员编号、工资；

第二步：此时发现有三列（dept表）同时发生着重复，那么就可以进行多字段分组。

4.HAVING 子句

现在要求查询出每个职位的名称，职位的平均工资，但是要求显示的平均工资大于2000.

即：按照职位先进行分组，同时统计出每个职位的平均工资；要求只显示出那些平均工资高于2000的职位信息。

错误分析：实质是没有搞清楚语句的执行顺序，WHERE子句上不允许出现统计函数，WHERE子句是在GROUP BY之前执行的。

此时的SQL语法结构：

【⑤选出所需要的数据列】SELECT [DISTINCT] * | 分组列[别名]，分组列 [别名]，分组列 [别名]…

【①确定数据来源】FROM 表名称 [别名]

【②筛选数据行】WHERE 限定条件（s）；

【③针对于筛选的行进行分组】GROUP BY 分组字段、分组字段、分组字段 …;

【④针对于筛选的行分组】HAVING 分组过滤

【⑥数据排序】ORDER BY 排序字段[ASC | DESC] ,排序字段 [ASC | DESC] …;

范例：使用HAVING子句

HAVING是在GROUP BY分组之后才执行的筛选，在HAVING里面可以直接使用统计函数。

说明：关于WHERE与HAVING的区别？

• WHERE子句是在GROUP BY分组之前进行筛选，指的是选出那些可以参与分组的数据，WHERE子句不允许使用统计函数。
• HAVING 子句是在GROUP BY分组之后执行的，可以使用统计函数。

5.分组案例

范例：显示所有非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要求满足从事同一工作月工资的合计大于5000，显示的结果按照月工资的合计的升序排列。

第一步：查询所有非销售人员的信息，WHERE进行限定查询。

第二步：按照职位进行分组，而后求出月工资的总支出；

第三步：分组后的数据进行再次的筛选，使用HAVING子句

第四步：按照月工资的合计升序排列，使用ORDER BY

范例：统计所有领取佣金和不领取佣金的人数、平均工资

按照简单的单细胞思维模式，现在肯定使用comm分组（只有无佣金的数据正确，因为分组的前提必须是数据重复）

把问题进行拆分：

• 查询出所有领取佣金的雇员的人数、平均工资，直接使用where子句。

  

• 查询出所有不领取佣金的雇员的人数、平均工资，直接使用where子句。

  

  既然此时两个查询结果返回的结构完全相同，那么可以直接进行连接