select * from tbname start with id=.. connect by prior superID=id;
SQL> desc t_tree; 名称 空? 类型 ---------------------------------------------------------------------- -------- -------- ID NUMBER PID NUMBERSQL> SQL> select * from t_tree; 1 0 11 1 12 1 111 11 112 11 1121 112 已用时间: 00: 00: 00.30 SQL> select * from t_tree start with id=11 connect by prior id=pid; 11 1 111 11 112 11 1121 112 已用时间: 00: 00: 00.20 SQL> select * from t_tree start with id=111 connect by prior id=pid; 111 11 已用时间: 00: 00: 00.10 SQL> select * from t_tree start with id=112 connect by prior id=pid; 112 11 1121 112 已用时间: 00: 00: 00.20 SQL>
层次化查询嘛,如果是在oracle 9i,可以直接用sys_connect_by_path(xxx,xxx)来完成,如下: select * from( select id,sys_connect_by_path(id,'->') path from Pub_ProcessInstance start with superid is null connect by prior id=superid) where id=xx;
非常感谢 bzszp(SongZip) 和 njhart2003() ,你们用的start with 以及connect by prior 等关键字我怎么在techonthenet上查询不到呢?你们能否发份PL/SQL语法大全给我呀,英文的也行……最近发觉用前台来处理业务逻辑时,速度很慢,可是对ORACLE存储过程又不熟悉。谢谢你们,我的MAIL是:[email protected]还有按照你们给出的方法不能得到所有相关节点ID,除非给定的节点ID是根节点ID,不过这对我来说暂时可以用其他方法代替就是了……再次感谢两位!还有bzszp,我看了你在CSDN中的Emag ORACLE专刊的文章,高手呀!
start with id=.. connect by prior superID=id;
名称 空? 类型
---------------------------------------------------------------------- -------- --------
ID NUMBER
PID NUMBERSQL>
SQL> select * from t_tree;
1 0
11 1
12 1
111 11
112 11
1121 112
已用时间: 00: 00: 00.30
SQL> select * from t_tree start with id=11 connect by prior id=pid;
11 1
111 11
112 11
1121 112
已用时间: 00: 00: 00.20
SQL> select * from t_tree start with id=111 connect by prior id=pid;
111 11
已用时间: 00: 00: 00.10
SQL> select * from t_tree start with id=112 connect by prior id=pid;
112 11
1121 112
已用时间: 00: 00: 00.20
SQL>
select * from(
select id,sys_connect_by_path(id,'->') path
from Pub_ProcessInstance
start with superid is null
connect by prior id=superid)
where id=xx;