05.分组查询

1. 介绍

引入：查询每个部门的平均工资

-- 以前写法：求的是总平均工资。
SELECT AVG(salary) FROM employees;
-- 正确写法
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;

2. 语法

SELECT 分组函数, 列 --列要求出现在 group by 的后面
FROM 表
[WHERE 筛选条件]
[GROUP BY 分组的列表]
[ORDER BY 列|分组函数];

注意： 查询列表必须特殊，要求是分组函数和 group by 后出现的字段。

一般出现 “每个”、“各个”、“每一个”、“所有” 等词语，都是要进行分组查询，并且这些词的后紧接的就是 ORDER BY 后的内容。

3. 简单分组函数

案例一：查询每个工种的最高工资

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

案例二：查询每个位置上的部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

2. 添加筛选条件

案例 1：普询邮箱中包含 a 字符的，每个部门的平均工资

SELECT AVG(salary), department_id
FROM employees
WHERE email like '%a%'
GROUP BY department_id;

案例 2:查询有奖金的每个领导手下员工的最高工资

SELECT MAX(salary), manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

3. 添加复杂的筛选条件

添加分组后的筛选：HAVING

案例 1: 查询哪个部门的员工个数>2

SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

案例 2：查询每个工种有奖金的员工的最高工资>12000 的工种编号和最高工资

-- 1. 查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
-- 2. 根据1结果继续筛选，最高工资>12000
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

案例 3:查询领导编号>102 的每个领导手下的最低工资>5000 的领导编号

 SELECT MIN(salary), manager_id
 FROM employees
 WHERE manager_id>102
 GROUP BY manager_id
 HAVING MIN(salary)>5000;

4. 分组查询特点

分组查询中的筛选条件分为两类：

分类	数据源	关键字	位置
分组前筛选	原始表	WHERE	GROUP BY
子句的前面
分组后筛选	分组后的结果集	HAVING	GROUP BY
子句的后面

1. 分组函数做条件肯定是放在 having 子句中
2. 能用分组前筛选的优先使用分组前筛选（性能好）
3. GROUP BY 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求）
4. 排序查询放在最后

5. 按表达式或函数分组

案例: 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5

-- a. 查询每个长度的员工个数
SELECT COUNT(*), LENGTH(last_name) AS len_name
FROM employees
GROUP BY LENGTH(last_name)
-- b. 添加筛选条件
SELECT COUNT(*) c, LENGTH(last_name) AS len_name
FROM employees
GROUP BY len_name
HAVING c>5;

6. 按多个字段分组

案例: 查询每个部门每个工种的员工的平均工资

SELECT AVG(salary), department_id, job_id
FROM employees
GROUP BY department_id, job_id;

7. 分组查询添加排序

案例: 查询每个部门每个工种的员工的平均工资，并且按平均工资的高低排列 排序查询放在最后

SELECT AVG(salary) a, department_id, job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id, job_id
HAVING a>10000
ORDER BY a DESC;