1. 引入案例：查询每个部门的平均工资
SELECT
`department_id`,
AVG(`salary`),
COUNT(*),
FROM
`employees`
GROUP BY `department_id` ;
2. 语法
select 分组函数,列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
3. 实例
基本使用
- 案例：查询每个工种的最高工资
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 |
- 分组函数做条件一定放在having子句中
- 能用分组前筛选的，就优先考虑使用分组前筛选
按表达式或函数分组
- 案例：按员工姓名长度分组，筛选员工个数大于5的
SELECT
LENGTH(`last_name`),
COUNT(*)
FROM
`employees`
GROUP BY LENGTH(`last_name`)
HAVING COUNT(*) > 5 ;
按多个字段分组
- 案例：查询每个部门每个工种的平均工资
SELECT
`department_id`,
`job_id`,
AVG(`salary`)
FROM
`employees`
GROUP BY `department_id`,
`job_id` ;
添加排序
- 案例：查询每个部门每个工种的平均工资，并按照平均工资排序，其中部门不为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
- group by子句支持单个字段分组，也支持多个字段分组（多个字段之间用逗号隔开，没有顺序），还支持表达式或函数分组
- 也可以添加排序（排序放在整个分组查询的最后）
