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

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

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行