connect by Nocycle Prior
select branchid from branch where parentbranchid='BRA0000000000001' order by branchid
第一句只能查询出父节点是BRA0000000000001的所有子节点
select br.branchid
from Branch br
start with br.branchid = 'BRA0000000000001'
connect by Nocycle Prior br.branchid = br.parentbranchid
这一句能查出父节点是BRA0000000000001的所有子节点,然后它的子节点的子节点都能查出来
语法:
[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition
select abbrname,branchid,A.PARENTBRANCHID,branchtype,branchstatus,LEVEL from branch a
where a.branchstatus='BRANCHSTATUS_1'
AND a.branchtype='BRANCHTYPE_30'
start with a.branchid='BRA0000000000002'
connect by nocycle prior a.branchid = a.parentbranchid;
小结:
测试时,不指定start with会出现重复记录,反而不指定NOCYCLE却没事
posted on 2012-06-01 15:35 lovebeauty 阅读(9270) 评论(0) 编辑 收藏 举报