ITPub博客

首页 > Linux操作系统 > Linux操作系统 > connect by与where条件执行顺序问题

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

原创 Linux操作系统 作者:redhouser 时间:2013-09-27 20:06:45 0 删除 编辑

-----------层次查询中查询条件在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    809303