MySQL笔记 06：分组查询

1. 引入案例

查询每个部门的平均工资

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

2. 语法

select 分组函数,列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】

3. 实例

---

3.1 基本使用

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

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

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

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

案例：查询邮箱中包含A字符的每个部门的平均工资

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

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

-- 分组前筛选条件
SELECT 
  `manager_id`,
  MAX(`salary`) 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL 
GROUP BY `manager_id` ;

案例：查询哪个部门的员工个数大于2

-- 分组后筛选条件
SELECT 
  `department_id`,
  COUNT(*) AS '员工个数' 
FROM
  `employees` 
GROUP BY `department_id` 
HAVING `员工个数` > 2 ;

案例：查询每个工种有奖金的员工的最高工资，且最高工资大于12000

SELECT 
  `job_id`,
  MAX(`salary`) 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL 
GROUP BY `job_id` 
HAVING MAX(`salary`) > 12000 ;

案例：查询领导编号大于102，手下的最低工资大于5000

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

---

总结 1

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

1. 分组函数做条件一定放在having子句中
2. 能用分组前筛选的，就优先考虑使用分组前筛选

---

3.2 按表达式或函数分组

案例：按员工姓名长度分组，筛选员工个数大于5的

SELECT 
  LENGTH(`last_name`),
  COUNT(*) 
FROM
  `employees` 
GROUP BY LENGTH(`last_name`) 
HAVING COUNT(*) > 5 ;

---

3.3 按多个字段分组

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

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

---

3.4 添加排序

案例：查询每个部门每个工种的平均工资，并按照平均工资排序，其中部门不为null且平均工资大于10000

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

---

总结2

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

---