例子:
select deptno, avg(sal), count(*)
from emp
where deptno != 20
group by deptno
having count(*) > 3 --having 进行二次筛选
order by avg(sal) desc;
where和having的区别:
区别1:where是分组之前的筛选,having是分组之后的筛选
区别2:having中可以出现分组函数,where中不可以
原因: 和执行顺序有关
执行顺序为:from where group by having select order by
group by 之后的语句都可以出现分组函数,group by之前的语句不可以出现分组函数
区别3:如果查询条件既可以在where也可以在having,建议使用where
各个语句的执行顺序:
from where group by having select order by
!! 注意:order by 是最后执行的
各个语句的书写顺序:
select from where group by having order by