006 group by having

/*
时间：2020/09/09
功能：
    一 group by 
    二 having
*/  

一 group by

select deptno, max(sal) "max", min(sal) "min", 
    avg(sal) "avg", sum(sal) "sum", 
    count(*) "all count", count(sal) "not null count" 
    from emp
    group by deptno

select deptno, job
    from emp
    group by deptno, job

select deptno, job, ename, mgr
    from emp
    group by deptno, job, ename, mgr

select deptno, job, min(sal) "部门最低工资", max(sal) "部门最高工资", 
    sum(sal) "部门总工资", avg(sal)"平均工资", count(*) "部门人数"
    from emp
    group by deptno, job
    order by deptno, job

二 having

--把姓名不包含A的所有的员工按部门编号分组，
--统计输出部门平均工资大于2000的部门的部门编号 部门的平均工资
select deptno, avg(sal)
    from emp
    where ename not like '%a%'
    group by deptno
    having avg(sal) > 2000


--把工资大于2000，
--统计输出部门平均工资大于3000的部门的部门编号 部门的平均工资
select deptno, avg(sal)
    from emp
    where sal > 2000
    group by deptno
    having avg(sal) > 3000

select deptno, job, avg(sal)
    from emp
    where hiredate >= '1981-05-01'
    group by deptno, job
    having deptno > 10
    order by deptno, job

select deptno, job, avg(sal)
    from emp
    where hiredate >= '1981-05-01'
    group by deptno, job
    having job like '%s%'
    order by deptno, job