# 进阶5:分组查询
/*
语法:
select 分组函数,列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
注意:
查询的列表必须特殊,要求是分组函数和group by后出现的字段
特点:
1、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后删选 分组后的结果集 group by子句的后面 having
1、分组函数做条件肯定是放在having子句中
2、能用分组前筛选的,就优先考虑使用分组前筛选
2、group by子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数(用的较少)
3、也可以添加排序(排序放在这个分组查询的最后)
*/
# 引人:查询每个部门的平均工资
select avg(salary) from employees;
select department_id,round(avg(salary),2) from employees group by department_id;
# 简单的分组查询
# 案例1:查询每个工种的最高工资
select job_id,max(salary) 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;
# 添加分组后的筛选条件
# 案例1:查询那个部门的员工个数 > 2
# 1、查询每个部门的员工个数
select count(*),department_id from employees group by department_id;
# 2、根据1的结果筛选,查询那个部门的员工个数>2
select count(*) con,department_id from employees group by department_id having con > 2;
# 案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
# 1、查询每个工种有奖金的员工最高工资
select max(salary),job_id
from employees
GROUP BY job_id;
# 2、根据1结果继续筛选,最高工资>12000
select max(salary),job_id
from employees
GROUP BY job_id
having max(salary) > 12000;
# 案例3:查询领导编号>102的每个领导手下的最低工资>5000的领导编号是那些,以及其最低工资
# 1、查询每个领导手下的员工最低工资
select min(salary),manager_id
from employees
group by manager_id;
# 2、添加筛选条件:编号 > 102
select min(salary),manager_id
from employees
where manager_id>102
group by manager_id;
# 3、添加筛选条件: 最低工资> 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) len_name
from employees
group by length(last_name);
# 2、添加筛选条件
select count(*),length(last_name) len_name
from employees
group by length(last_name)
having count(*)>5;
#-----------------------
select count(*) c,length(last_name) len_name
from employees
group by len_name
having c>5;
# 按多个字段分组
# 案例:查询每个部门每个工种的员工的平均工资
select avg(salary),department_id,job_id
from employees
group by job_id,department_id;
# 添加排序
# 案例:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
select avg(salary),department_id,job_id
from employees
group by job_id,department_id
order by avg(salary) desc;
# 测试
# 1. 查询个job_id的员工工资的最大值,最小值,平均值,总和,并且按job_id升序
select job_id,max(salary),min(salary),avg(salary),sum(salary)
from employees
group by job_id
order by job_id asc;
# 2. 查询员工最高工资和最低工资的差距(defference)
select max(salary),min(salary),max(salary)-min(salary) defference from employees;
# 3. 查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不能计算在内
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) >= 6000;
# 4. 查询所有部门的编号,员工数量和工资平均值,并按平均工资降序
select department_id,count(*) 员工数,round(avg(salary),2) 平均值
from employees
group by department_id
order by avg(salary) desc;
# 5. 选择具有各个job_id的员工人数
select job_id,count(*)
from employees
group by job_id;