connect by与where条件执行顺序问题

最新推荐文章于 2025-02-13 17:29:28 发布

转载最新推荐文章于 2025-02-13 17:29:28 发布 · 2.1k 阅读

标签

#数据库

本文深入探讨了SQL中的层次查询概念，包括如何使用CONNECT BY子句和HIERARCHICAL_QUERY_CLAUSE，重点介绍了在查询条件、连接条件及层次结构中的应用，通过实例展示了不同查询条件对结果的影响。

-----------层次查询中查询条件在connect by后执行

文档：
《SQL REFERENCE》
SELECT/hierarchical_query_clause
The hierarchical_query_clause lets you select rows in a hierarchical order.
SELECT statements that contain hierarchical queries can contain the LEVEL pseudocolumn in the select list. LEVEL returns the value 1 for a root node, 2 for a child node of a root node, 3 for a grandchild, and so on. The number of levels returned by a hierarchical query may be limited by available user memory.
Oracle processes hierarchical queries as follows:
（1）A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.
（2）The CONNECT BY condition is evaluated.
（3）Any remaining WHERE clause predicates are evaluated.
If you specify this clause, then do not specify either ORDER BY or GROUP BY, because they will destroy the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause.

举例：
--无查询条件
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
start with mgr is null
connect by prior empno=mgr;
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    JONES    /KING/JONES
3    SCOTT    /KING/JONES/SCOTT
4    ADAMS    /KING/JONES/SCOTT/ADAMS
5    FORD    /KING/JONES/FORD
6    SMITH    /KING/JONES/FORD/SMITH
7    BLAKE    /KING/BLAKE
8    ALLEN    /KING/BLAKE/ALLEN
9    WARD    /KING/BLAKE/WARD
10    MARTIN    /KING/BLAKE/MARTIN
11    TURNER    /KING/BLAKE/TURNER
12    JAMES    /KING/BLAKE/JAMES
13    CLARK    /KING/CLARK
14    MILLER    /KING/CLARK/MILLER

--查询条件在connect by后执行
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
where ename<>'KING'
start with mgr is null
connect by prior empno=mgr;
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    JONES    /KING/JONES
2    SCOTT    /KING/JONES/SCOTT
3    ADAMS    /KING/JONES/SCOTT/ADAMS
4    FORD    /KING/JONES/FORD
5    SMITH    /KING/JONES/FORD/SMITH
6    BLAKE    /KING/BLAKE
7    ALLEN    /KING/BLAKE/ALLEN
8    WARD    /KING/BLAKE/WARD
9    MARTIN    /KING/BLAKE/MARTIN
10    TURNER    /KING/BLAKE/TURNER
11    JAMES    /KING/BLAKE/JAMES
12    CLARK    /KING/CLARK
13    MILLER    /KING/CLARK/MILLER

--connect by中的条件在除过第一行外执行
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
start with mgr is null
connect by prior empno=mgr and ename<>'KING';
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    JONES    /KING/JONES
3    SCOTT    /KING/JONES/SCOTT
4    ADAMS    /KING/JONES/SCOTT/ADAMS
5    FORD    /KING/JONES/FORD
6    SMITH    /KING/JONES/FORD/SMITH
7    BLAKE    /KING/BLAKE
8    ALLEN    /KING/BLAKE/ALLEN
9    WARD    /KING/BLAKE/WARD
10    MARTIN    /KING/BLAKE/MARTIN
11    TURNER    /KING/BLAKE/TURNER
12    JAMES    /KING/BLAKE/JAMES
13    CLARK    /KING/CLARK
14    MILLER    /KING/CLARK/MILLER

--connect by中的条件在除过第一行外执行，可以过滤掉一个分支
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
start with mgr is null
connect by prior empno=mgr and ename<>'JONES';
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    BLAKE    /KING/BLAKE
3    ALLEN    /KING/BLAKE/ALLEN
4    WARD    /KING/BLAKE/WARD
5    MARTIN    /KING/BLAKE/MARTIN
6    TURNER    /KING/BLAKE/TURNER
7    JAMES    /KING/BLAKE/JAMES
8    CLARK    /KING/CLARK
9    MILLER    /KING/CLARK/MILLER

--查询条件放入子查询，效果与将查询条件放在connect by中（除了第一行外）
select rownum, ename, sys_connect_by_path(ename, '/')
from (select * from scott.emp where ename <> 'KING')
start with mgr is null
connect by prior empno = mgr;

no rows selected

--查询条件放入子查询，效果与将查询条件放在connect by中（除了第一行外）
select rownum, ename, sys_connect_by_path(ename, '/')
from (select * from scott.emp where ename <> 'JONES')
start with mgr is null
connect by prior empno = mgr;
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    BLAKE    /KING/BLAKE
3    ALLEN    /KING/BLAKE/ALLEN
4    WARD    /KING/BLAKE/WARD
5    MARTIN    /KING/BLAKE/MARTIN
6    TURNER    /KING/BLAKE/TURNER
7    JAMES    /KING/BLAKE/JAMES
8    CLARK    /KING/CLARK
9    MILLER    /KING/CLARK/MILLER

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-773506/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-773506/