实例介绍oracle树结构查询

实例介绍oracle树结构查询

2520发表于2015-04-05

有时我们的数据结构要求是树的形式,比如分类表、地区。下面我用实际的例子介绍一下oracle树结构查询。


1、查询指定树节点得所有子节点(area为parentID):
select level,t.* from unit t connect by prior unitid=area start with unitid='f17066ed-4eae-4bba-b2c2-22a9cb6749b2'
order by level


2、查询指定树节点得父节点(area为parentID)
select level,t.* from unit t connect by prior area=unitid start with unitid='3d105ac9-22fb-4d81-bc23-97dafedb0021'
order by level



3、缩进树查询结果
select level,rpad(' ',level*5)||unitname from unit t start with unitid='f17066ed-4eae-4bba-b2c2-22a9cb6749b2' 
connect by prior unitid =area



4、树分支的过滤
select level,rpad(' ',level*5)||unitname from unit t start with unitid='f17066ed-4eae-4bba-b2c2-22a9cb6749b2' 
connect by prior unitid =area and t.unitname<>'高新区'


5、过滤掉单个节点
select level,rpad(' ',level*5)||unitname from unit t 
where t.unitname<>'成都市'
start with unitid='f17066ed-4eae-4bba-b2c2-22a9cb6749b2' 
connect by prior unitid =area and t.unitname<>'高新区'

小编蓝狐