Oracle——connect by prior 递归算法

实例1：

select dm,dm,level cjdm,
sys_connect_by_path(dm,'/')
 from (
select '1' sjdm,'11' dm from dual
union all
select '11' sjdm,'111' dm from dual
union all
select '111' sjdm,'1111' dm from dual
union all 
select '111' sjdm,'1112' dm from dual
union all
select '1111' sjdm,'11111' dm from dual
union all
select '11111' sjdm,'111111' dm from dual
union all
select '111111' sjdm,'1111111' dm from dual
union all 
select '111111' sjdm,'1111112' dm from dual)
connect by prior dm = sjdm
start with sjdm = 1
order by sjdm

--或者换种写法

select dm,dm,level cjdm,
sys_connect_by_path(dm,'/')
 from (
select '1' sjdm,'11' dm from dual
union all
select '11' sjdm,'111' dm from dual
union all
select '111' sjdm,'1111' dm from dual
union all 
select '111' sjdm,'1112' dm from dual
union all
select '1111' sjdm,'11111' dm from dual
union all
select '11111' sjdm,'111111' dm from dual
union all
select '111111' sjdm,'1111111' dm from dual
union all 
select '111111' sjdm,'1111112' dm from dual)
start with sjdm = 1
connect by (prior dm)= sjdm
order by sjdm

结果：

实例2：如果结果中有循环行的话需要使用NOCYCLE

select dm,dm,level cjdm,connect_by_iscycle,connect_by_isleaf,
sys_connect_by_path(dm,'/')
 from (
select '1' sjdm,'11' dm from dual
union all
select '11' sjdm,'111' dm from dual
union all
select '111' sjdm,'1111' dm from dual
union all 
select '111' sjdm,'1112' dm from dual
union all 
select '1112' sjdm,'111' dm from dual
union all
select '1111' sjdm,'11111' dm from dual
union all
select '11111' sjdm,'111111' dm from dual
union all
select '111111' sjdm,'1111111' dm from dual
union all 
select '111111' sjdm,'1111112' dm from dual)
connect by nocycle prior dm = sjdm
start with sjdm = 1
order by sjdm

查询结果：

 

 

总结：

START WITH：子句为可选项，用来标识哪行作为查找树型结构的第一行（即根节点，可指定多个根节点）。若该子句被省略，则表示所有满足查询条件的行作为根节点。

PRIOR：查询父行的限定符，格式: PRIOR COLUMN1 = COLUMN2 OR COLUMN1 = PRIOR COLUMN2 AND ... ，置于运算符前后的位置，决定着查询时的检索顺序。

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为层级最高节点