having子句(分组后的限制条件)

• 查询 所有每个部门中最高薪水 大于2900 的才输出

SELECT
    DEPTNO,
    MAX(sal)
FROM
    emp
GROUP BY
    DEPTNO
HAVING
    MAX(sal) > 2900

select 语句执行的顺序

1. from 子句中找到先要查询的表
2. where子句进行费分组函数的筛选判断
3. group by 子句完成分组
4. having完成组函数筛选
5. select 语句显示列或者组函数
6. order by 子句进行排序操作

基础sql

SELECT deptno,  job , sal from emp

增加where

SELECT
    deptno,
    job,
    sal
FROM
    emp
WHERE
    JOB IN ('MANAGER', 'SALESMAN', 'CLERK')

增加group by

SELECT
    deptno,
    job,
    sal
FROM
    emp
WHERE
    JOB IN ('MANAGER', 'SALESMAN', 'CLERK')
GROUP BY DEPTNO, job, sal

增加having

SELECT
    deptno,
    JOB, 
    SAL, 
    AVG(SAL)
FROM
    emp
WHERE
    JOB IN ('MANAGER', 'SALESMAN', 'CLERK')
GROUP BY DEPTNO, JOB, SAL
HAVING AVG(SAL) > 1000

正常的select

SELECT
    deptno,
    JOB, 
    AVG(SAL)
FROM
    emp
WHERE
    JOB IN ('MANAGER', 'SALESMAN', 'CLERK')
GROUP BY DEPTNO, JOB, 
HAVING AVG(SAL) > 1000

增加 orderby

查询 岗位是 (MANAGER', 'SALESMAN', 'CLERK') 每个部门的 部门编号/岗位/平均工资 并且每个组的平均工资要大于1000 最后按平均工资进行降序排序

SELECT
    deptno,
    JOB, 
    AVG(SAL)
FROM
    emp
WHERE
    JOB IN ('MANAGER', 'SALESMAN', 'CLERK')
GROUP BY DEPTNO, JOB 
HAVING AVG(SAL) > 1000
ORDER BY 3 DESC;

• 查询部门人数大于2的部门编号，部门名称，部门人数

select e.deptno , d.dname,count(e.empno)
from emp e,dept d
where e.deptno=d.deptno
group by e.DEPTNO
HAVINg count(e.empno)>2

• 查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

select e.deptno , d.dname,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by e.DEPTNO,d.dname
HAVINg avg(e.sal)>2000 and count(e.EMPNO)>2
order by 3 asc