sys_connect_by_path的理解

显示全部楼层 · 2007-9-26 17:05:46

SELECT REPLACE(SYS_CONNECT_BY_PATH(f.addr_NAME, '\')||DECODE (f.zip_code, NULL, NULL, ' 邮编:' || f.zip_code),'\','')addr_all
FROM (SELECT e.addr_NAME,e.addr_id,e.paraddr_id,e.zip_codeFROM ibss.TB_RM_STD_ADDR e CONNECT BY e.ADDR_ID = PRIORe.PARADDR_ID
START WITHe.ADDR_ID =5307102) f WHERE f.addr_id=5307102 START WITH f.PARADDR_ID = 0
CONNECT BY PRIOR f.addr_ID = f.PARADDR_ID
这句SQL,请问WHERE f.addr_id=5307102 比START WITH f.PARADDR_ID = 0
CONNECT BY PRIOR f.addr_ID = f.PARADDR_ID先执行吗？
还是START WITH f.PARADDR_ID = 0
CONNECT BY PRIOR f.addr_ID = f.PARADDR_ID执行完毕后，在从中取出WHERE f.addr_id=5307102 的？
在线急等！！！

千问 · 2007-9-26 17:05:46

怎么没有人回答，顶下

千问 · 2007-9-26 17:05:46

一起执行吧

千问 · 2007-9-26 17:05:46

不是吧
SELECT REPLACE(SYS_CONNECT_BY_PATH(f.addr_NAME, '\')||DECODE (f.zip_code, NULL, NULL, ' 邮编:' || f.zip_code),'\','') addr_all
FROM (SELECT e.addr_NAME,e.addr_id,e.paraddr_id,e.zip_code FROM ibss.TB_RM_STD_ADDR e CONNECT BY e.ADDR_ID = PRIOR e.PARADDR_ID
START WITH e.ADDR_ID =5307102) f WHERE f.addr_id=5307102 START WITH f.PARADDR_ID = 0
CONNECT BY PRIOR f.addr_ID = f.PARADDR_ID
这个SQL如果没有WHERE f.addr_id=5307102 的话，那么就会显示十几条记录，但加上了之后，就会显示一条记录，我的意思是说是不是通过START WITH f.PARADDR_ID = 0
CONNECT BY PRIOR f.addr_ID = f.PARADDR_ID执行之后，在结果集里面用WHERE f.addr_id=5307102 过虑了绝大部分记录，只显示一条记录呢

千问 · 2007-9-26 17:05:46

where先执行

千问 · 2007-9-26 17:05:46

不是吧
从运行的结果来看
where 后面的最后执行的

千问 · 2007-9-26 17:05:46

SQL> select * from tt;
ID
MC
-------------------- --------------------
1
1
2
2
3
3
1
5
2
6
3
7
6 rows selected
SQL> select * from tt start with id='1' connect by mc = prior mc + 1;
ID
MC
-------------------- --------------------
1
1
2
2
3
3
1
5
2
6
3
7
6 rows selected
SQL> select * from tt where rownum

千问 · 2007-9-26 17:05:46

测试如下：
SQL> Select * From Test_My;
C1 C2 C3 C4
---------- ---------- ---------- --------
1
2
3 a
1
2
4 a
1
2
5 a
1
2
3 b
1
2
4 b
1
2
5 b
2
3
4 c
2
3
5 c
2
3
4 d
2
3
5 d
1
2
3 e
1
2
4 e
1
2
3 f
1
2
4 f
3
6
9 k
1
3
3 a
3
5
3 a
17 rows selected
先执行where 部分
SQL> SELECT * From (
2Select *
3FROM Test_My
4WHEREC4 = 'a'
5)
6STARTWith C3 = '3'
7CONNECT BY Prior C2 = c1;
C1 C2 C3 C4
---------- ---------- ---------- --------
1
2
3 a
1
3
3 a
3
5
3 a
3
5
3 a
后执行where 相当于下边代码
SQL> SELECT * From (
2Select *
3FROM Test_My
4STARTWith C3 = '3'
5CONNECT BY Prior C2 = c1
6)WHEREC4 = 'a';
C1 C2 C3 C4
---------- ---------- ---------- --------
1
2
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
1
3
3 a
3
5
3 a
3
5
3 a
20 rows selected
一起执行
SQL> Select *
2FROM Test_My
3WHEREC4 = 'a'
4STARTWith C3 = '3'
5CONNECT BY Prior C2 = c1;
C1 C2 C3 C4
---------- ---------- ---------- --------
1
2
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
1
3
3 a
3
5
3 a
3
5
3 a
20 rows selected
因为和一致而和不一致
所以应该是先执行connect by 而后执行where部分

千问 · 2007-9-26 17:05:46

按照一般常理来说，都会认为（至少我这么认为）先执行where比较合理！但是结果就不是这样子的哈！

看来connect也有不尽人意的地方哈！

千问 · 2007-9-26 17:05:46

最初由 myfriend2010 发布
[B]测试如下：
SQL> Select * From Test_My;
C1 C2 C3 C4
---------- ---------- ---------- --------
1
2
3 a
1
2
4 a
1
2
5 a
1
2
3 b
1
2
4 b
1
2
5 b
2
3
4 c
2
3
5 c
2
3
4 d
2
3
5 d
1
2
3 e
1
2
4 e
1
2
3 f
1
2
4 f
3
6
9 k
1
3
3 a
3
5
3 a
17 rows selected
先执行where 部分
SQL> SELECT * From (
2Select *
3FROM Test_My
4WHEREC4 = 'a'
5)
6STARTWith C3 = '3'
7CONNECT BY Prior C2 = c1;
C1 C2 C3 C4
---------- ---------- ---------- --------
1
2
3 a
1
3
3 a
3
5
3 a
3
5
3 a
后执行where 相当于下边代码
SQL> SELECT * From (
2Select *
3FROM Test_My
4STARTWith C3 = '3'
5CONNECT BY Prior C2 = c1
6)WHEREC4 = 'a';
C1 C2 C3 C4
---------- ---------- ---------- --------
1
2
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
1
3
3 a
3
5
3 a
3
5
3 a
20 rows selected
一起执行
SQL> Select *
2FROM Test_My
3WHEREC4 = 'a'
4STARTWith C3 = '3'
5CONNECT BY Prior C2 = c1;
C1 C2 C3 C4
---------- ---------- ---------- --------
1
2
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
3
5
3 a
1
3
3 a
3
5
3 a
3
5
3 a
20 rows selected
因为和一致而和不一致
所以应该是先执行connect by 而后执行where部分 [/B]

怎么看出where后执行？