SQL执行顺序
看到一篇讲sql语句执行顺序的文章，觉得比较有意思，自己测试之后和大家分享一下
让我们通过一个有趣的统计，来看看SQL语句的执行顺序。
正好有一些数据资源，那选取大家应该会比较有兴趣的话题，哪个星座的空姐最多？
选取的表CREW_V
因为一般一个星座的开始都是在当月的20到23号，因为在做统计的时候，我在每个出生日期-22天，这样，就可能通过月份来代表星座
例如处女座是在8月23号到9月23号，那-22天后，只要统计出来生日在8月的，就可以粗略的认为这个人是处女座
看下面的几段SQL语句：
--统计空姐的出生月份
SELECT TO_CHAR(BIRTH_DT-22, 'MM' ), COUNT(TO_CHAR(BIRTH_DT-22, 'MM' )) AS BIRTH
FROM CREW_V
WHERE SEX = 'F'
AND NON_CREW_IND = 'N'
GROUP BY BIRTH
--统计空姐的出生月份，当月出生人数大于100的才记录
SELECT TO_CHAR(BIRTH_DT-22, 'MM' ),
COUNT (TO_CHAR(BIRTH_DT-22, 'MM' )) AS BIRTH
FROM CREW_V
WHERE SEX = 'F' AND NON_CREW_IND = 'N'
GROUP BY TO_CHAR(BIRTH_DT-22, 'MM' )
HAVING COUNT (BIRTH) > 100
--按照月份出生的人数排序
SELECT TO_CHAR(BIRTH_DT-22, 'MM' ), COUNT(TO_CHAR(BIRTH_DT-22, 'MM')) AS BIRTH
FROM CREW_V
WHERE SEX = 'F'
AND NON_CREW_IND = 'N'
GROUP BY TO_CHAR(BIRTH_DT-22, 'MM' )
ORDER BY BIRTH
你觉得哪一个不能够成功执行？
示例一
SELECT TO_CHAR(BIRTH_DT, 'MM' ), COUNT(TO_CHAR(BIRTH_DT, 'MM' )) AS BIRTH
FROM CREW_V
WHERE SEX = 'F'
AND NON_CREW_IND = 'N'
GROUP BY BIRTH
实际执行顺序：
1、FROM CREW_V
2、WHERE SEX = 'F' AND NON_CREW_IND = 'N'
3、GROUP BY BIRTH
4、SELECT TO_CHAR(BIRTH_DT, 'MM' ), COUNT(TO_CHAR(BIRTH_DT, 'MM' )) AS BIRTH
很明显，执行GROUP BY BIRTH时别名BIRTH还没有创建，因此它是不能执行成功的。
示例二
SELECT TO_CHAR(BIRTH_DT, 'MM' ),
COUNT (TO_CHAR(BIRTH_DT, 'MM' )) AS BIRTH
FROM CREW_V
WHERE SEX = 'F' AND NON_CREW_IND = 'N'
GROUP BY TO_CHAR(BIRTH_DT, 'MM' )
HAVING COUNT (BIRTH) > 100
这是非常基础的分组查询。但它不能执行成功，因为HAVING的执行顺序在SELECT之上。
实际执行顺序如下：
- FROM CREW_V
- WHERE SEX = 'F' AND NON_CREW_IND = 'N'
- GROUP BY TO_CHAR(BIRTH_DT, 'MM' )
- HAVING COUNT (BIRTH) > 100
-
SELECT TO_CHAR(BIRTH_DT, 'MM' ), COUNT (TO_CHAR(BIRTH_DT, 'MM' )) AS BIRTH
很明显，BIRTH是在最后一句SELECT TO_CHAR(BIRTH_DT, 'MM' ), COUNT (TO_CHAR(BIRTH_DT, 'MM' )) AS BIRTH执行过后生成的新别名。
因此，在HAVING COUNT (BIRTH) > 100执行时是不能识别BIRTH的。
示例三
SELECT TO_CHAR(BIRTH_DT, 'MM' ), COUNT(TO_CHAR(BIRTH_DT, 'MM')) AS BIRTH
FROM CREW_V
WHERE SEX = 'F'
AND NON_CREW_IND = 'N'
GROUP BY TO_CHAR(BIRTH_DT, 'MM' )
ORDER BY BIRTH
这一次没有任何问题，能够成功执行。
1、 FROM CREW_V
2、 WHERE SEX = 'F' AND NON_CREW_IND = 'N'
3、 GROUP BY TO_CHAR(BIRTH_DT, 'MM' )
4、 SELECT TO_CHAR(BIRTH_DT, 'MM' ), COUNT(TO_CHAR(BIRTH_DT, 'MM')) AS BIRTH
5、 ORDER BY BIRTH
下面是SELECT语句的逻辑执行顺序：
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE or WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP
下面给大家分享一点私密的数据
