在Oracle中的树形操作

在Oracle中的树形操作

 

1.取子节点及路径(正树):
select t.id ,t.code, t.name ,t.pid
,SYS_CONNECT_BY_PATH(t.id,'.')||'.' as IdPath
from tas_catalog t
--where id!=110
start with id=110
connect by  pid = prior id
order siblings  by id

 

2.取各级父节点(倒树)：
select t.id ,t.code, t.name ,t.pid
,SYS_CONNECT_BY_PATH(t.id,'.')||'.' as IdPath --路径从反的
from tas_catalog t
--where id!=110
start with id=110
connect by  id = prior pid
order siblings  by id

 

无论正树还是倒树, 关键就在于connect by的条件.
正树:   必须是   ‘父’= prior ‘子’
倒树:   必须是   ‘子’= prior ‘父’