oracle的树stats with,ORACLE START WITH 语句的树级结构例子

原创 转载请注明出处

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

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

8684                   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行