selectcount(*),location_id from departments groupby location_id;
添加筛选条件
案例1:查询邮箱中包含 a 字符的 ,每个部门的平均工资
selectavg(salary),depatment_id,email from employees where email like'%a%'groupby department_id;
案例2:查询有奖金的每个领导手下员工的最高工资
selectmax(salary),manager_id
from
employees
where
commission_pct isnotnullgroupby
manager_id;
添加分组后的筛选条件
案例1:查询哪个部门的 员工>2
1、查询每个部门的员工个数
2、根据1的结果筛选,查询哪个部门的员工个数>2/*
select
count(*),department_id
from
employees
where
count(*)>2
group by
department_id; #此方法报错,where不能使用分组函数作为条件
*/selectcount(*),department_id
from
employees
groupby
department_id
havingcount(*)>2;
2、查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select
job_id,max(salary)from
employees
where
commission_pct isnotnullgroupby
job_id
havingmax(salary)>12000;
案例3:查询领导编号>102的每个领导手下的最低工资>5000的领导编号
select
manager_id,min(salary)from
employees
where
manager_id>102groupby
manager_id
havingmin(salary)>5000;
按表达式或函数分组
按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
selectcount(*),length(last_name)from
employees
groupby
length(last_name)havingcount(*)>5;# group by 和 havng 子句后面都支持别名,但是where后面不支持,不常用selectcount(*) c,length(last_name) len_name
from
employees
groupby
len_name
having
c>5;
按多个字段分组
查询每个部门每个工种的员工的平均工资
# 若按多个字段分组,则将多个字段都放在 group by 后面即可,顺序可以颠倒,相当于只有者几个数据相等时才是一组selectavg(salary),department_id,job_id
from
employees
groupby department_id,job_id;
添加排序
案例:查询每个部门每个工种的平均工资,并且按平均工资的高低显示
selectavg(salary),department_id,job_id
from
employees
groupby
department_id,job_id
orderbyavg(salary)desc;