Oracle中查询关键字select--from--where--group by--having--order by执行顺序

     select--from--where--group by--having--order by 这6个查询关键字的执行顺序：

1.from组装来自不同数据源的数据；
2.where基于指定的条件对记录行进行筛选；
3.group by子句将数据划分为多个分组；
4.使用聚集函数进行计算；
5.使用having子句筛选分组；
6.计算所有的表达式；
7.select 集合输出；
8.使用order by对结果集进行排序。

 

以下例子基于Oracle自带表空间hr

例子1：having和select的执行说明

情景：关于哪个部门员工最多？

A

SELECT D1.DEPARTMENT_ID,D2.DEPARTMENT_NAME
FROM EMPLOYEES D1 JOIN DEPARTMENTS D2 ON D1.DEPARTMENT_ID=D2.DEPARTMENT_ID
GROUP BY D1.DEPARTMENT_ID ,D2.DEPARTMENT_NAME
HAVING count(*)>= all(SELECT count(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)

B

SELECT D1.DEPARTMENT_ID,D2.DEPARTMENT_NAME,count(*)
FROM EMPLOYEES D1 JOIN DEPARTMENTS D2 ON D1.DEPARTMENT_ID=D2.DEPARTMENT_ID
GROUP BY D1.DEPARTMENT_ID ,D2.DEPARTMENT_NAME
HAVING count(*)>= all(SELECT count(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)

A、B结果相同，说明having先于select执行