start with...connect by子句递归查询一般用于一个表维护树形结构的应用。
创建示例表：
create table tbl_test
(
id number,
name varchar2(100 byte),
pid number default 0
);
插入测试数据：
insert into tbl_test(id,name,pid) values('1','10','0');
insert into tbl_test(id,name,pid) values('2','11','1');
insert into tbl_test(id,name,pid) values('3','20','0');
insert into tbl_test(id,name,pid) values('4','12','1');
insert into tbl_test(id,name,pid) values('5','121','2');
从root往树末梢递归
select * from tbl_test
start with id=1
connect by prior id = pid
从末梢往树root递归
select * from tbl_test
start with id=5
connect by prior pid = id
=====
对于oracle进行简单树查询(递归查询)
deptid paredeptid name
number number char (40 byte)
部门id 父部门id(所属部门id) 部门名称
通过子节点向根节点追朔.
sql代码
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
sql代码
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
通过根节点遍历子节点.
sql代码
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
sql代码
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
可通过level 关键字查询所在层次.
sql代码
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
sql代码
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
再次复习一下:start with ...connect by 的用法， start with 后面所跟的就是就是递归的种子。
递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询；
connect by prior 后面所放的字段是有关系的，它指明了查询的方向。
练习： 通过子节点获得顶节点
sql代码
select first_value(deptid) over (order by level desc rows unbounded preceding) as firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid
====这种方法只是当表里就有一颗树,多棵树怎么办？
声明：javaeye文章凯发k8网页登录的版权属于作者，受法律保护。没有作者书面许可不得转载。
回复