分组查询

进阶5：分组查询

分组数据：group by 子句语法，可以将表中的数据分成若干组

语法：
select 分组函数 ，列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
注意：查询列表必须特殊，要求是分组函数和group by 后面出现的字段，where一定放在from后面
特点：

1. 分组查询中的筛选条件分为两类：分组钱筛选，分组后筛选

类别	数据源	位置	关键字
分组前筛选	原始表	group by子句前面	where
分组后筛选	分组后的结果集	group by子句后面	having

注意：
（1）分组函数做条件肯定是放在having子句中
（2）能用分组前筛选的，就优先考虑使用分组前筛选

2. group by子句支持单个字段分组，也支持多个字段分组（多个字段用逗号隔开，没有顺序要求），也可以表达式或函数分组（用的较少）
3. 也可以添加排序（排序放在整个分组查询最后的）

一、简单的分组查询

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

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

结果：

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

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

结果：

二、添加分组前的筛选条件

案例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 ;

结果：

三、添加分组后的筛选条件（用having）

案例1：查询哪个部门的员工个数 > 2
分步讨论：

1. 查询每个部门的员工个数

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

2. 根据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的领导编号是哪个，以及其最低工资

1. 查询领导变>102的每个领导手下的员工最低工资

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

结果：

2. 根据1结果筛选，最低工资>5000

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

结果：

四、按表达式或函数分组

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

1. 查询每个长度的员工个数

SELECT 
  COUNT(*),
  LENGTH(last_name) 姓名长度 
FROM
  employees 
GROUP BY LENGTH(last_name) ;

结果：

2. 根据1结果筛选员工个数>5的有哪些

SELECT 
  COUNT(*),
  LENGTH(last_name) 姓名长度 
FROM
  employees 
GROUP BY LENGTH(last_name) 
HAVING COUNT(*)>5;

结果：

五、按多个字段分组

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

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

结果：

六、添加排序

案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示

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

结果：

测试

（1）查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序

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

结果：

（2）查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT 
  MAX(salary) - MIN(salary) AS 差距 
FROM
  employees ;

结果：

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

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

结果：

（4）查询所有部门的编号，员工数量和工资平均值，并按平均工资降序

SELECT 
  COUNT(*),
  department_id,
  AVG(salary) 
FROM
  employees 
GROUP BY department_id 
ORDER BY AVG(salary) DESC ;

结果：

（5）选择具有各个job_id的员工人数

SELECT 
  COUNT(*) AS 员工个数,
  job_id 
FROM
  employees 
GROUP BY job_id ;

结果：