Having:
/*查询哪个部门的员工数>2
select count (*),department_id
from employees
group by department_id
having count(*)>2;
*/
此处不能用where,因为count不属于表中列名
可记为分组函数做条件肯定是放在having子句中
能用分组前筛选,优先考虑使用分组前筛选
按表达式或者函数分组
按员工姓名长度分组,查询每一组员工个数,筛选员工个数大于5
①查询每个姓名长度的员工个数
select count(*),length(name)len_name
from employees
group by length(name)
②添加筛选条件
select count(*),length(name)len_name
from employees
group by length(name)
having count(*)>5
③按多个字段进行分组
查询每个部门每个工种的员工平均工资
select avg(salary),department_id,job_id
from employees
group by department_id,job_id;
④添加排序
查询每个部门每个工种的员工平均工资(超过1w),并且按平均工资高低显示
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;