语法
select 分组函数,列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
特点:
1、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的数据集 group by子句的后面 having
(1)分组函数做条件肯定是放在having子句中
(2)能用分组前筛选的,最优先考虑使用分组前筛选
2、group by支持单字段分组,多字段分组,表达式分组
group by
(1)简单的分组查询
#例1:查询每个工种的最高工资
select max(salary), job_id
from employees
group by job_id;
#例2,查询每个位置上的部门个数
select count(*), location_id
from departments
group by location_id;
(2)添加筛选条件
#例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;
(3)添加分组后的筛选条件
#查询哪个部门的员工个数>2
#(1)查询每个部门的员工个数
select count(*),department_id
from employees
group by department_id;
#(2)根据(1)的结果进行筛选,查询哪个部门的员工个数大于2
select count(*),department_id
from employees
group by department_id
having count(*)>2;
#例2,查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select job_id,max(salary)
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;
#例3,查询领导编号大于102的每个领导手下的最低工资>5000的领导编号是哪个,以及最低工资
select manager_id,min(salary)
from employees
where manager_id>102
group by manager_id
having min(salary)>5000;
(4)按表达式或者函数分组
#例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
select count(*),length(last_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;
(5)按多个字段分组
#例: 查询每个部门每个工种的员工平均工资
select department_id,job_id,avg(salary)
from employees
group by department_id,job_id
(6)添加排序
#例:查询每个部门每个工种的员工平均工资,并且按平均工资的高低显示
select department_id,job_id,avg(salary)
from employees
where department_id is not null
group by department_id,job_id
having avg(salary)>10000
order by avg(salary) desc;