基本语法
select...from tabename start with cond1 connect by prior cond2 where cond2
注:cond1是根节点的限定语句
cond2是连接条件,其中prior表示上一条记录,指该记录的父亲是上一条记录
cond3是过滤条件
构造环境:不说你懂的
create table Family(
id integer,
parentid integer,
name varchar2(50)
)
insert into family values(0,0,'a')
insert into family values(1,0,'b')
insert into family values(2,1,'c')
insert into family values(3,1,'d')
insert into family values(4,1,'e')
insert into family values(5,1,'f')
例一:通过根节点遍历子节点
--查询父亲等于1的所有子的信息
select * from family start with parentid=1 connect by prior id=parentid
例二:通过子节点向根节点追溯
select * from family start with id=5 connect by prior parentid=id
注:如果报ORA-01436:用户数据库中的coonect by循环,则将第一条数据中的parentid改为null,否则loop循环找parentid就找不到了!
扩展:通过level 关键字查询所在层次
select t.*,level from family t start with parentid=1 connect by prior id=parentid --表必须用别名