oracle 怎么来遍历一个树,相比较其他方法,oracle的connect语法更能很便利的解决问题。
语法格式:
select ...
from ...
start with...
connect by prior expr=expr
order siblings by ..
start with 的功能类似于where,指明从哪个分支开始便利;
connect by 指明父节点和子节点地连接方式,关键字prior放在父节点之前;
order siblings by代表对同一层的节点进行排序方法。
例子:
select * from direct
start with parentid is null
connect by prior parentid = id;
结果:
id name parentid
1 oracle null
2 assistants 1
3 bin 1
4 RDBMS 1
5 ADMIN 4
6 catcr.sql 5
7 catawrvw.sql 5
如果想看树的层次结构,可用oracle提供LEVEL伪列
方法如下:
select level,lpad(' ',level * 2 -1)|| '|' || name name
from direct
start with parentid is null
connect by prior id = parentid;
使用SYS_CONNECT_BY_PATH,取得之前的路径信息
select id,SYS_CONNECT_BY_PATH(name,'/') name
from direct
start with parentid is null
connect by prior id = parentid;