轉(zhuǎn)載自：http://community.csdn.net/Expert/topic/4998/4998917.xml?temp=.2047846
表結(jié)構(gòu)如下：
該表只有兩個(gè)字段，PNODE，NODE，PNODE為NODE父結(jié)點(diǎn)，當(dāng)該節(jié)點(diǎn)無(wú)子節(jié)點(diǎn)時(shí)，PNODE=NODE
要求給定父結(jié)點(diǎn)，查出所有子節(jié)點(diǎn)，子孫節(jié)點(diǎn)......
如：
PNODE NODE
??? 1??? 2
??? 2??? 3
??? 2??? 4
??? 3??? 3
??? 4??? 4
要求給定PNODE=1，給出結(jié)果為：
2
3
4
如果是10G以上的可以用NOCYCLE 去掉循環(huán)的影響
....
start with PNODE = 1
connect by NOCYCLE prior NODE = PNODE
....
10G以下的可以試試：
start with PNODE = 1
connect by prior NODE = PNODE
where NODE <> PNODE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> create table tab_a (pnode number(2),node number(2));
Table created
SQL> insert into tab_a values (1,2);
1 row inserted
SQL> insert into tab_a values (2,3);
1 row inserted
SQL> insert into tab_a values (2,4);
1 row inserted
SQL> insert into tab_a values (3,3);
1 row inserted
SQL> insert into tab_a values (4,4);
1 row inserted
SQL> commit;
Commit complete
----- oracle9i的: 構(gòu)造tab 以避免循環(huán)
SQL> with
? 2? tab as (
? 3??? select pnode,decode(node-pnode,0,null,node) node
? 4??? from tab_a
? 5? )
? 6? select distinct pnode
? 7? from tab
? 8? where pnode >1
? 9? start with pnode=1
?10? connect by prior node=pnode;
PNODE
-----
??? 2
??? 3
??? 4