MySQL-分组查询

分组查询

语法

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

查询列表必须是分组函数和group by后出现的字段

案例

简单分组查询

1、查询每个工种的最高工资

select max(salary),jod_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;

添加复杂的筛选条件

1、查询哪个部门的员工个数大于2

• 查询每个部门的员工个数
• 查询哪个部门的员工个数大于2

select count(*),department_id
from employees
group by department_id
having count(*)>2;

2、查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资

• 查询每个工种有奖金的员工的最高工资
• 对结果进行筛选，最高工资大于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的领导编号，以及最低工资

• 查询每个领导手下的最低工资
• 筛选出领导编号大于102的
• 筛选最低工资大于5000的

select min(salary),manager_id
from employees
where manager_id>102
group by manager_id
having min(salary)>5000;

按表达式或函数分组

1、按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些

select count(*) c,length(last_name) len_name
from employees
group by len_name
having c>5;

按多个字段分组

1、查询每个部门每个工种的员工的平均工资

select avg(salary),department_id,job_id
from employees
group by department_id,job_id;

添加排序

1、查询每个部门每个工种的员工的平均工资

select avg(salary),department_id,job_id
from employees
where department_id is not null
group by department_id,job_id
having avg(salary)>10000
order by avg(salary) desc;