四.having子句

1.语法:

select 查询内容

        from 表名

        [where 条件]

        [group by 分组字段]

        [having 条件]

        [order by 字段名asc|desc]

select from --->where过滤--->group by --->having过滤

--  1.列如: 每个部门不同岗位人数，且人数大于2

select count(*) from emp where count(*)>2 group by deptno,job

select deptno,job,count(*) from emp group by deptno,job having count(*)>2

--  2.列如:在emp表中工资最小值小于2000的职位

select job,min(sal)from emp group by job having min(sal)<2000

--  3.列如:列出平均工资大于1200的部门和职位搭配组合

select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)>1200