聚合函数

GROUP BY 的使用

需求：查询各个部门的平均工资，最高工资
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id;
需求：查询各个 job_id 的平均工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;
需求：查询各个 department_id,job_id 的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
GROUP BY 声明在 FROM 后面、WHERE 后面，ORDER BY 前面、LIMIT 前面
需求：查询各个部门的平均工资，按照平均工资升序排列
SELECT department_id,AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_sal ASC;

HAVING 的使用 (作用：用来过滤数据的)

练习：查询各个部门中最高工资比 10000 高的部门信息

#错误的写法：
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;

要求 1：如果过滤条件中使用了聚合函数，则必须使用 HAVING 来替换 WHERE。否则，报错。

要求 2：HAVING 必须声明在 GROUP BY 的后面。

SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

要求 3：开发中，我们使用 HAVING 的前提是 SQL 中使用了 GROUP BY。

练习：查询部门 id 为 10,20,30,40 这 4 个部门中最高工资比 10000 高的部门信息

#推荐，执行效率高
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在 HAVING 中。

当过滤条件中没有聚合函数时，则此过滤条件声明在 WHERE 中或 HAVING 中都可以。但是，建议大家声明在 WHERE 中。

WHERE 与 HAVING 的对比

1. 从适用范围上来讲，HAVING 的适用范围更广。

2. 如果过滤条件中没有聚合函数：这种情况下，WHERE 的执行效率要高于 HAVING

SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

聚合函数的课后练习

1.where 子句可否使用组函数进行过滤?

No

2.查询公司员工工资的最大值，最小值，平均值，总和

SELECT MAX(salary) max_sal ,MIN(salary) mim_sal,AVG(salary) avg_sal,SUM(salary) sum_sal
FROM employees;

3.查询各 job_id 的员工工资的最大值，最小值，平均值，总和

SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;

4.选择具有各个 job_id 的员工人数

SELECT job_id,COUNT(\*)
FROM employees
GROUP BY job_id;

5.查询员工最高工资和最低工资的差距（DIFFERENCE） #DATEDIFF

SELECT MAX(salary) - MIN(salary) "DIFFERENCE"
FROM employees;

6.查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内

SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

SELECT d.department_name,d.location_id,COUNT(employee_id),AVG(salary)
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name,location_id

8.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT d.department_name,e.job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name,job_id