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;
--正向递归查询(根据id查询自己和自己一下的数据)
- select * from table
- start with id=9842
- connect by prior id= parentId
- order by id
- select * from table
- start with id=9842
- connect by nocycle prior parentId=id
- order by id