orace递归查询

 

--查询结果自己所有的前代节点（包括自己）。

select * from orgTable o
where nvl(o.canceled,0)<>1
start with o.id='5661' connect by to_char(o.id)=prior to_char(o.supsubcomid)

 

--查询结果自己所有的后代节点（包括自己）。

select * from orgTable o where o.supsubcomid is not null
AND nvl(o.canceled,0)<>1
start with o.id='5661'
connect by prior to_char(o.id)=to_char(o.supsubcomid)

--查询结果自己所有的后代节点（不包括自己）。

select * from orgTable o where o.id is not null
AND nvl(o.canceled,0)<>1
start with o.supsubcomid='5661'
connect by prior to_char(o.id) =to_char(o.supsubcomid )

--查询结果自己的第一代后节点和所有的前代节点（包括自己）。

select o.* from orgTable o
where nvl(o.canceled,0)<>1
start with o.supsubcomid='5661'
connect by to_char(o.id )= prior to_char(o.supsubcomid)