分组查询
select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;
分组查询 group by
分组筛选 Having
特点
- 1: 和分组函数一同查询的字段必须是 group by 后出现的字段
- 筛选 having 分为两类分组前和分组后筛选
针对的表 | 位置 | 连接的关键字 | |
---|---|---|---|
分组前筛选 | 原始表 | group by 前 | where |
– | – | ||
分组后筛选 | group by 后的结果集 | group by 后 | having |
1 :分组函数做条件的肯定是放在having子句中
2: 能用分组函数前筛选的就优先考虑使用分组前筛选
- 分组可以按多个字段也可以按多个字段,多个字段之间用逗号隔开没有顺序要求
- 可以搭配排序使用(排序放在整个分组查询的最后)
分组的筛选是难点
我们要看筛选的条件是不是 原始表所具备的如果不是原始表所有的字段,那么就放在分组后的子句后使用关键字 having, 如果是原始表内的字段就放在from表后使用where关键字,分组函数做筛选条件肯定是放在having子句后
分组查询的分析
1 : 要仔细分析是对哪个字段 哪个条件来分组,然后分析哪个条件是用来筛选的 再分析筛选的条件是否是原始表内所具有的
order by having 要先把筛选条件剔除 一步步来做 最后再将 筛选条件放在having后
- eg:
基础分组查询
-- 查找邮箱中包含a字符的,每个部门的平均薪资
select avg(salary) 平均薪资, department_id from employees where email like "%a%" group by department_id;
-- 查询有奖金的每个领导的手下员工的最高薪资
-- 解答: 我们可以知道每个领导 那就是按照领导来分组
select MAX(salary) 最高薪资, manager_id from employees group by manager_id;
复杂分组
# 案例1 : 查询哪个部门的员工个数大于2
-- 分为两步解答
-- a 先查询每个部门的员工个数
select count(*),department_id from employees GROUP BY department_id;
-- b 根据a的结果进行筛选,查询哪个部门的员工个数大于2,
-- 下面是错误的写法 ,因为count(*) 是没有在表中的字段 所以会报错 我们可以在查询完分组后的语句后使用count(*)>2因为这个时候第一步查完的语句是由count(*) 这个时候就用到了分组后筛选 having
select COUNT(*) 个数, department_id from employees where COUNT(*) > 2 group by department_id;
-- 正确写法
select COUNT(*) 个数, department_id from employees GROUP BY department_id HAVING COUNT(*) > 2;
# 案例2 查询每个工种有奖金的员工的最高工资
select MAX(salary) 最高薪资, job_id from employees where commission_pct is not null group by job_id;
# 案例3 查询每个工种有奖金的员工的最高工资> 12000的工种编号和最高薪资
-- a 查询每个工种有奖金的员工的最高薪资
select MAX(salary), job_id from employees group by job_id;
-- 下面查询是否有奖金,奖金字段在原表中是有的 所以放在 from子句后面使用where关键字
-- b根据a的加过继续筛选 最高薪资> 12000 因为最高薪资在原始表中没有 所以放在分组后的表后 使用having关键字
select MAX(salary) 最高薪资, job_id from employees WHERE commission_pct is not null group by job_id HAVING MAX(salary) > 12000;
# 查询领导编号>102的每个领导手下的最低工资> 5000的领导编号是哪个,以及其最低薪资
-- a查询每个领导手下员工的最低薪资
select MIN(salary), manager_id from employees group by manager_id;
-- b : 筛选条件 编号 > 102 因为领导编号在原始表中存在,所以放在from子句后 使用where关键字
select MIN(salary) 最低薪资, manager_id from employees where manager_id > 102 GROUP BY manager_id;
-- c . 添加筛选条件 最低薪资 > 5000
SELECT MIN(salary) 最低薪资, manager_id from employees where manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000;
按多个字段分组
- 多个分组条件就在中间加上,逗号 隔开
- group by 条件1,条件2
-- 查询每个部门每个工种的工作的平均薪资
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;
** 由上可以看出order by 一般都是放在除了limit的最后所以排序放在分组后 **
Practices
#1.查询各job_id的员工工资的最大值,最小值,平均值,总和,并按job_id升序
select MAX(salary) 最高薪资, MIN(salary) 最低薪资, AVG(salary) 平均薪资, SUM(salary) 总薪资 from employees GROUP BY job_id order by job_id
#2.查询员工最高工资和最低工资的差距(DIFFERENCE)
select MAX(salary) 最高薪资, MIN(salary) 最低薪资, (MAX(salary) - MIN(salary)) DIFFERENCE from employees
-- select MAX(salary) as gao, MIN(salary) as di, ( 'gao' - 'di') DIFFERENCE from employees; 这里是不可以的 因为 gao 和di 字段在原始表是不存在的 是无法使用在from前的 如果使用在from后是可以的 因为from后就是
# 3.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
select MIN(salary) 最低薪资, job_id from employees where manager_id is not null GROUP BY job_id HAVING 最低薪资 >= 6000;
select MIN(salary) 最低薪资, job_id FROM employees where manager_id is not null GROUP BY job_id HAVING MIN(salary) >= 6000;
#4.查询所有部门的编号,员工数量和工资平均值,并按平均工资降序
select COUNT(*) 员工数量, AVG(salary) 平均薪资, department_id 部门编号 from employees GROUP BY department_id ORDER BY 平均薪资 desc
#5.选择具有各个job_id的员工人数
select COUNT(*) 人数,job_id FROM employees GROUP BY job_id;