having字句

用来对多行函数的结果进行过滤
having 和 where作用相同，都是条件过滤
where过滤普通条件，最早执行
having过滤多行函数结果，分组，求完多行函数后，才执行

select count(*) from emps; -- 只有一个结果，不需要过滤

有多行结果的才需要过滤,如对下：

-- 按主管id分组，求每个主管的手下人数
select mgr_id,count(*) as totalpeople
from emps
where mgr_id is not null
group by mgr_id;

要对count(*)过滤,对多行函数的结果过滤,having前必须有groupby

select mgr_id,count(mgr_id) as totalpeople
from emps
where mgr_id is not null
group by mgr_id
having totalpeople=1;

注意在group by后面添加having

-- 平均工资小于等于5000的岗位代码
select job_id ,avg(sal) as lowsal
from emps
group by job_id
having lowsal<=5000;