原创 转载请注明出处
connect by 是结构化查询中用到的，其基本语法是：
select ... from tablename start by cond1
connect by cond2
where cond3;
考虑如下语句
select *
from t_agency
where VALIDATE_STATUS = 'Y'
start with organ_id =1152
connect by parent_id = prior agency_id;
select parent_id,agency_id,organ_id from t_agency;
PARENT_ID AGENCY_ID ORGAN_ID
----------- ----------- ----------------------------------------
81 1
81 82 1152
82 84 1152006
83 85 1688
81 83 1688
59 1152
60 1152
60 61 1152
84 86 1152
图1
第一步查询会查找出
SQL> select *
2 from t_agency
3 where VALIDATE_STATUS = 'Y'
4 and organ_id =1152
5 ;
会出现5行
AGENCY_ID PARENT_ID ORGAN_ID
----------- ----------- ----------------------------------------
82 81 1152
59 1152
60 1152
61 60 1152
86 84 1152
图2
第二步通过connect by parent_id = prior agency_id;
进行向子及衍生。
然后条件变为parent_id in(82,59,60,61,86)
及查询
SQL> select agency_id,parent_id,organ_id
2 from t_agency
3 where VALIDATE_STATUS = 'Y'
4 and parent_id in(82,59,60,61,86);
AGENCY_ID PARENT_ID ORGAN_ID
----------- ----------- ----------------------------------------
84 82 1152006
61 60 1152
得到2行，可以看出此两行来自于 父节点
82 81 1152
60 1152
第三步同样的操作条件变为parent_id in(84,61)
SQL> select agency_id,parent_id,organ_id
2 from t_agency
3 where VALIDATE_STATUS = 'Y'
4 and parent_id in(84,61)
5 ;
AGENCY_ID PARENT_ID ORGAN_ID
----------- ----------- ----------------------------------------
86 84 1152
得到1行，可以看出此两行来自于 父节点
84 82 1152006
第四步继续田间变为parent_id =86
SQL> select agency_id,parent_id,organ_id
2 from t_agency
3 where VALIDATE_STATUS = 'Y'
4 and parent_id =86
5 ;
AGENCY_ID PARENT_ID ORGAN_ID
----------- ----------- ----------------------------------------
至此树形结构形成，并且中止。
82 59 60 61 86
84 61
86
形成了8行
