Oracle中start with connect by prior 使用方法

一、语法

{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...} 
解释: 
start with: 指定起始节点的条件 
connect by: 指定父子行的条件关系 
prior: 查询父行的限定符，格式: prior column1 = column2 or column1 = prior column2 and ... ， 
nocycle: 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条 循环行: 该行只有一个子行，而且子行又是该行的祖先行 
connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是 
connect_by_isleaf: 是否是叶子节点，0表示否，1 表示是 
level: level伪列,表示层级，值越小层级越高，level=1为层级最高节点 

二、使用

oracle的start with connect by prior是根据条件递归查询"树"，分为四种使用情况：

第一种：查询结果自己所有的后代节点（包括自己）

start with 子节点ID='...' connect by prior 子节点ID = 父节点ID 
select * from mdm_organization o start with o.org_code='10000008' connect by prior o.org_code=o.org_parent_code

按照条件org_code='10000008'，对'10000008'（包括自己）及其子节点进行递归查询，结果如下：

 第二种：查询结果自己所有的前代节点（包括自己）

start with 子节点ID='...' connect by 子节点ID = prior 父节点ID 
select * from mdm_organization o start with o.org_code='10000008' connect by o.org_code=prior o.org_parent_code

按照条件org_code='10000008'，对'10000008'（包括自己）及其父节点进行递归查询，结果如下：

 第三种：查询结果自己所有的后代节点（不包括自己）

start with 父节点ID='...' connect by prior 子节点ID = 父节点ID 
select * from mdm_organization o start with o.org_parent_code='10000008' connect by prior o.org_code=o.org_parent_code

按照条件org_parent_code='10000008'，对'10000008'（不包括自己）子节点进行递归查询，结果如下：

 第四种：查询结果自己的第一代后节点和所有的前代节点（包括自己）

start with 父节点ID='...' connect by 子节点ID = prior 父节点ID 
select * from mdm_organization o start with o.org_parent_code='10000008' connect by o.org_code = prior o.org_parent_cod

按照条件org_parent_code='10000008'，对'10000008'（包括自己）的第一代孩子们及其父节点进行递归查询，结果如下：

 如果有where 条件，如下：

select * from mdm_organization o where 条件 start with o.org_parent_code='10000008' connect by o.org_code = prior o.org_parent_code

执行顺序为先执行start with connect by prior，然后再按照where条件进行过滤。

该部分参考（oracle的start with connect by prior如何使用），具体请前往查看。

三、函数

1、sys_connect_by_path函数

语法：sys_connect_by_path(列名, '分隔符')。
作用：从start with的地方开始遍历，将遍历到的路径根据函数中的分隔符，组成一个新的字符串。

select sys_connect_by_path(ename, '/') ename_tree from scott.emp
start with ename = 'KING'
connect by mgr = prior empno;

sys_connect_by_path函数使用的一个小技巧，把查询行转换成列，把表emp的所有列名以'|'分隔开输出（提示：大家可以把下面的语句拆开来逐个分析），SQL语句如下：

select max(ltrim(sys_connect_by_path(column_name, '|'), '|')) column_names from (select column_name, rownum rnum
from user_tab_columns
where table_name = 'EMP')
start with rnum = 1
connect by rnum = rownum;

2、level函数

在结构化查询结果中，每一行都是结构中的一个节点，level表示该节点在结构中的层次，根节点为1，根节点的子节点为2，以此类推。
下面SQL语句很直观的展示效果：

select ename, sys_connect_by_path(ename, '/') ename_tree, level
from scott.emp
start with ename = 'KING'
connect by mgr = prior empno;

3、connect_by_root函数

用在列名之前，返回当前节点的根节点对应列的值。

下面SQL语句很直观的展示效果：

select connect_by_root ename as root
from scott.emp e
start with e.ename = 'KING'
connect by prior e.empno = e.mgr;

4、connect_by_isleaf函数

返回当前节点是否为叶子节点，“是”返回1，“否”返回0。

下面SQL语句很直观的展示效果：

select connect_by_isleaf as isleaf
from scott.emp e
start with e.ename = 'KING'
connect by prior e.empno = e.mgr;