一、简介
在oracle中start with connect by (prior) 用来对树形结构的数据进行查询。其中start with conditon 给出的是数据搜索范围, connect by后面给出了递归查询的条件,prior 关键字表示父数据,prior 条件表示子数据需要满足父数据的什么条件。如下
start with id= ‘10001’ connect by prior parent_id= id and prior num = 5
表示查询id为10001,并且递归查询parent_id=id,为5的记录。
二、实例
–prior放的左右位置决定了检索是自底向上还是自顶向下. 左边是自上而下(找子节点),右边是自下而上(找父节点)
–找父节点
select * from emp e start with e.empno=7369 connect by empno = prior mgr
select * from emp e start with e.empno=7788 connect by prior empno = mgr
使用level显示层次:
select level,empno,mgr,ename from emp
start with empno=7839
connect by prior empno=mgr
order by level;
裁剪节点:使用where条件
select level,empno,mgr,ename from emp
where empno<>7782
start with empno=7839
connect by prior empno=mgr
order by level
裁剪分支:
select level,empno,mgr,ename from emp
start with empno=7839
connect by prior empno=mgr
and empno<>7782
order by level