标题: connect by与where条件执行顺序 [打印本页] 作者: 张兴康 时间: 2020-3-21 14:12 标题: connect by与where条件执行顺序 --无查询条件
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
