SQL語句匯總-Connect by

5.樹的使用(connect by)
表        tree
欄位      master sub sales
insert into tree values('主1',     '主2',   15);   insert into tree values('主1',     '主3',   20);           
insert into tree values('主2',     '主4',    5);    insert into tree values('主2',     '主5',   10);
insert into tree values('主3',     '主5',   30);   insert into tree values('主3',     '主6',   40);

SQL> select * from tree;
MASTER     SUB             SALES
---------- ---------- ----------
主1        主2                15
主1        主3                20
主2        主4                 5
主2        主5                10
主3        主5                30
主3        主6                40  
如果用樹型結構表示如下:
  '主1'                    
      -'主2'               
         --'主4'
         --'主5'
  '主1'                    
      -'主3'               
         --'主5'
         --'主6'
SQL> select * from tree                                 
start with sub='主2'          --相當於普通sql的where條件
connect by prior master=sub;  --遍曆的順序是sub先於master遍曆，也就是說從sub往上遍曆一直到master(根節點)
  2  
MASTER     SUB             SALES
---------- ---------- ----------
主1        主2                15
SQL> select * from tree   start with master='主2'
connect by prior master=sub; --sub往上遍曆至根節點（參考一下樹型圖）                             
  2    3  
MASTER     SUB             SALES            
---------- ---------- ----------            
主2        主4                 5   --這條是自己本身，也就是第一遍遍曆
主1        主2                15   --這是第2次遍曆，我們從樹型圖可以看到，'主2'往上遍曆是'主1'         
主2        主5                10            
主1        主2                15            
                                         
好，我們關看上面可能還是不好理解，我們加入一個樹結構專用函數sys_connect_by_path，便於理解
SQL> select sys_connect_by_path(MASTER,'/') from tree   --master表示我遍曆的起點只找在master列中存在的，如下例只要'主2'為起點，並以/為分割符
start with master='主2'
connect by prior master=sub;   --往根節點遍曆
  2    3  
SYS_CONNECT_BY_PATH(MASTER,'/')
--------------------------------------------------------------------------------
/主2                          --第1遍遍曆
/主2/主1                    --第2遍遍曆
/主2                          --第2條master='主2'的記錄的第1次遍曆
/主2/主1                    --第2條master='主2'的記錄的第2次遍曆

SQL> select sys_connect_by_path(MASTER,'/') from tree --起點為sub='主5'時MASTER=主2，主3
start with sub='主5'
connect by prior master=sub;
  2    3  
SYS_CONNECT_BY_PATH(MASTER,'/')
--------------------------------------------------------------------------------
/主2
/主2/主1
/主3
/主3/主1

SQL> select sys_connect_by_path(MASTER,'/'),sub,master from tree     
start with sub is not null
connect by prior master=sub;  
  2    3  
SYS_CONNECT_BY_PATH(MASTER,'/' SUB        MASTER
------------------------------ ---------- ----------
/主1                           主2        主1  --找主1到根的路徑,這雷根是主1他自己
/主1                           主3        主1  
/主2                           主4        主2  
/主2/主1                     主2        主1  --找主2到根的路徑,這雷根是主1
/主2                           主5        主2
/主2/主1                     主2        主1  
/主3                           主5        主3  
/主3/主1                     主3        主1
/主3                           主6        主3
/主3/主1                     主3        主1   

select sys_connect_by_path(MASTER,'/'),sub,master from tree                                 
start with sub is not null
connect by prior sub = master;                                               

SYS_CONNECT_BY_PATH(MASTER,'/' SUB        MASTER
------------------------------ ---------- ----------
/主1                           主2        主1
/主1/主2                     主4        主2
/主1/主2                     主5        主2
/主1                           主3        主1
/主1/主3                     主5        主3
/主1/主3                     主6        主3
/主2                           主4        主2
/主2                           主5        主2
/主3                           主5        主3
/主3                           主6        主3