1. 分组查询

语法：

select 分组函数,列（要求出现在group by 后面） 
from 表 
[where ...] 
group by 分组的列表 
[order by ...] 
[limit ....]

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

select max(salary), job_id
from employees
group by job_id

2、每个位置上的部门个数

select count(*), location_id
from locations
group by location_id
;

3、添加筛选条件

查询邮箱中包含a字符的，每个部门的平均工资：

select avg(salary), department_id
from employees
where email like '%a%'
group by department_id;

4、查询有奖金的每个领导手下员工的最高工资

select max(salary), manager_id
from employees
where commission_pct is not null
group by manager_id;

复杂的筛选条件

1、查询哪个部分的员工数>2

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

这里就只能使用having，判断个数大于2；

2、查询每个工种有奖金的员工的最高工资>12000的工种

select max(salary) s, job_id
from employees
where commission_pct is not null
group by job_id
having s > 12000;

3、查询领导编号>102的每个领导手下的最低工资>5000的领导编号，以及其最低工资；

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

按多个字段分组

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

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

添加排：

2。查询每个部门的每个工种的员工的平均工资，并按工资排序

select avg(salary) s, department_id, job_id
from employees
where department_id is not null
group by department_id, job_id, job_id
order by s desc

2. 总结

特点：

筛选条件两种：where、having

1、where用于分组前筛选，位于group by语句前面；

2、having用于分组后筛选，位于group by语句后面；