oracle树中prior的用法

小咪咪 2021-06-26 16:06 514阅读 0赞

数据说明一切：

create table dept(deptno number,deptname varchar2(20),mgrno number); insert into dept values (1, '总公司', null); insert into dept values (2, '浙江分公司', 1); insert into dept values (3, '杭州分公司', 2); commit; select t.* from dept t; DEPTNO DEPTNAME MGRNO 1 1 总公司 2 2 浙江分公司 1 3 3 杭州分公司 2

select * from dept connect by prior mgrno=deptno ; --以子结点为开始向根结点遍历 DEPTNO DEPTNAME MGRNO 1 1 总公司 2 2 浙江分公司 1 3 1 总公司 4 3 杭州分公司 2 5 2 浙江分公司 1 6 1 总公司

select * from dept start with mgrno=1 connect by prior mgrno=deptno ---以子结点为开始且开始为1，的遍历 DEPTNO DEPTNAME MGRNO 1 2 浙江分公司 1 2 1 总公司

--------------------

select from dept connect by prior deptno= mgrno DEPTNO DEPTNAME MGRNO 1 2 浙江分公司 1 2 3 杭州分公司 2 3 3 杭州分公司 2 4 1 总公司 5 2 浙江分公司 1 6 3 杭州分公司 2

select * from dept start with mgrno=1 connect by prior deptno= mgrno DEPTNO DEPTNAME MGRNO 1 2 浙江分公司 1 2 3 杭州分公司 2

## 总结： ##

1）`prior`放在子节点端，则表示扫描树是以`start with`指定的节点作为根节点从上往下扫描。可能对应一个或多个分支。  
`start with`可以省略，如果省略，表示对所有节点都当成根节点分别进行遍历  
2）`prior`放在父节点端，则表示扫描树是以`start with`指定的节点作为最低层子节点，从下往上扫描。顺序是子节点往父节点扫描，直到根节点为止，这种情况只能得到一个分支。  
`start with`可以省略，如果省略，表示对所有节点都当成最低层子节点分别往根节点方向遍历

最后会了上面大家应该知道下面 `sys_connect_path()`

`sys_connect_by_path` 函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示：

select/* max(substr(*/sys_connect_by_path(deptname,',')/*,2))*/ from dept connect by prior deptno= mgrno; SYS_CONNECT_BY_PATH(DEPTNAME,' 1 ,浙江分公司 2 ,浙江分公司,杭州分公司 3 ,杭州分公司 4 ,总公司 5 ,总公司,浙江分公司 6 ,总公司,浙江分公司,杭州分公司

--------------------

## oracle递归查询子节点 ##

通过子节点向根节点追朔.

select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid

通过根节点遍历子节点(不包含根节点).

select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid

通过根节点遍历子节点(包含根节点).

select * from persons.dept start with deptid=0 connect by prior deptid=paredeptid

可通过`level` 关键字查询所在层次.

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` 后面所放的字段是有关系的，它指明了查询的方向。

**练习： 通过子节点获得顶节点**

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

表结构

DEPTID PAREDEPTID NAME NUMBER NUMBER CHAR (40 Byte) 部门id 父部门id(所属部门id) 部门名称