轉載自：http://community.csdn.net/Expert/topic/4998/4998917.xml?temp=.2047846
表結構如下：
該表只有兩個字段，PNODE，NODE，PNODE為NODE父結點，當該節點無子節點時，PNODE=NODE
要求給定父結點，查出所有子節點，子孫節點......
如：
PNODE NODE
??? 1??? 2
??? 2??? 3
??? 2??? 4
??? 3??? 3
??? 4??? 4
要求給定PNODE=1，給出結果為：
2
3
4
如果是10G以上的可以用NOCYCLE 去掉循環的影響
....
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的: 構造tab 以避免循環
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