分组查询
特点: 分组查询的筛选条件可以分为两类
分组前的筛选 group by 子句的前面 关键字是where
和分组后的筛选 group by 子句的后面 关键字是having
分组函数做条件肯定是放在having 子句中,能用分组前筛选优先考虑
语法:
select 分组函数,列(要求出现
在group by的后面)
from 表
group by 分组的列表
order by 子句
引例:查询每一个工种的最高工资
select max(salary),job_id
from employees
group by job_id;
引例:查询每一个部门上的部门个数
select count(*) ,location_id
from departments
group by location_id;
引例:查询邮箱中包含a字符的,每个部门的平均工资
select avg(salary),department_id
from employees
where email like'%a%'
group by department_id;
案例二:
查询有奖金的每个领导的手下员工的最高工资
select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id
#添加复杂的筛选条件
案例一:查询那个部门的员工个数>2
分析:
1.查询每个部门员工的个数
select count(*),department_id
from employees
group by department_id;
2.根据一的结果筛选,查询哪个部门的员工个数>2
select count(*),department_id
from employees
group by epartment_id
having count (*)>2 #添加分组后的筛选
案例二:查询每个工种的有奖金的员工,最高工资>12000的工种编号和最高工资
分析:
1.查询每个工种有奖金的员工的最高工资
select max(salary),job_id
from employees
where commission_pct is not null
group by job_id;
2.根据1的结果进行筛选,最高工资>12000
select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
having max(salary) >12000;
#按表达式或者函数分组
#案例:
按员工的姓名的长度分组,查询每一个员工的个数,筛选员工个数大于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 avg(salary),department_id,job_id
from employees
group by job_id,department_id;
添加排序
select avg(salary),department_id,job_id
from employees
groupby job_id,department_id
order by avg(salary) desc;