oracle樹狀結構層級查詢小結--connect by等

oracle樹狀結構層級查詢小結--connect by等

一、簡介：

（1）oracle樹狀結構查詢即層次遞歸查詢，是sql語句經常用到的。

（2）樹狀結構通常由根節點、父節點、子節點和葉節點組成，簡單來說，一張表中存在兩個字段，dept_id,par_dept_id,那麼通過找到每一條記錄的父級id即可形成一個樹狀結構，也就是par_dept_id=dept_id，通俗的說就是這條記錄的par_dept_id是另外一條父級記錄的dept_id，其樹狀結構層級查詢的基本文法是：

　　SELECT [LEVEL],*
　　FEOM table_name 
　　START WITH 條件1
　　CONNECT BY PRIOR 條件2
　　WHERE 條件3
　　ORDER　BY　排序字段;
           

說明：LEVEL---僞列，用于表示樹的層次

　　   條件1---根節點的限定條件，當然也可以放寬權限，以獲得多個根節點，也就是擷取多個樹

　　  條件2---連接配接條件，目的就是給出父子之間的關系是什麼，根據這個關系進行遞歸查詢

　　  條件3---過濾條件，對所有傳回的記錄進行過濾

　　   排序字段---對所有傳回記錄進行排序

（3）關鍵字解析：

1）start with 子句：周遊起始條件，如果要查父節點，這裡可以用子結點的列，如果要查子節點，這裡可以用父節點的列。

2）connect by 子句：連接配接條件。

3）prior：

要的時候有兩種寫法：connect by prior dept_id=par_dept_id 或 connect by dept_id=prior par_dept_id，

前一種寫法表示采用自上而下的搜尋方式（先找父節點然後找子節點），

後一種寫法表示采用自下而上的搜尋方式（先找葉子節點然後找父節點）。 

也可以這樣了解：

prior跟父節點列parentid放在一起，就是往父結點方向周遊；

prior跟子結點列subid放在一起，則往葉子結點方向周遊；

 parentid、subid兩列誰放在“=”前都無所謂，關鍵是prior跟誰在一起。

4）order by 子句：排序

二、實戰解析，測試資料

部落格位址：oracle樹狀結構層級查詢小結之測試資料 https://blog.csdn.net/dboy_willow/article/details/81630485

三、測試資料查詢（部分資料）

select * from SYS_DEPT;
           

四、實戰示範

在這張表中有三個字段：

部門主鍵id：dept_id ;     部門名稱：dept_name  ;    部門編碼：dept_code ;      父級部門id(首級部門為 -1): par_dept_id;

1.connect by level 取連續數值

select level from dual t connect by level < = 5;
           

2.目前節點周遊子節點（周遊目前部門下所有子部門包括本身）

select t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level
  from SYS_DEPT t
 start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'
connect by prior t.dept_id = t.par_dept_id
 order by level, t.dept_code;
           

結果：dept_id=40288ac45a3c1e8b015a3c28b4ae01d6 是客運部主鍵，對其下的所有子部門進行周遊；

同時用  order by level,dept_code 進行排序 以便達到實際生活中想要的資料；共31條資料，部分資料如圖所示：

2.目前節點周遊子節點（周遊目前部門下部分子部門）：在上面的資料中擷取層級在3,也就是level=3的所有子部門。

select t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level
  from SYS_DEPT t
 where level = '3'
 start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'
connect by prior t.dept_id = t.par_dept_id
 order by level, t.dept_code;
           

3.目前節點周遊父節點（周遊目前部門上面所有父級部門）

select t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level
  from SYS_DEPT t
 start with t.dept_id = '4028e4d35b5ca4ee015b6138b84b2e68'
connect by prior t.par_dept_id = t.dept_id
 order by level, t.dept_code;
           

4.sys_connect_by_path函數:求父節點到子節點路徑

簡單介紹下，在oracle中sys_connect_by_path與connect by 一起使用，也就是先要有或建立一棵樹，否則無用還會報錯;

它的主要作用展現在path上即路徑，是可以把個父節點下的所有節點通過某個字元區分，然後連結在一個列中顯示;

sys_connect_by_path(column,clear),其中column是字元型或能自動轉換成字元型的列名;

它的主要目的就是将父節點到目前節點的“path”按照指定的模式出現;

char可以是單字元也可以是多字元，但不能使用列值中包含的字元;

而且這個參數必須是常量，且不允許使用綁定變量，clear不要用逗号。

select ltrim(sys_connect_by_path(t.dept_name, '-->'), '-->'),
       t.dept_id,
       t.dept_name,
       t.dept_code,
       t.par_dept_id,
       level
  from SYS_DEPT t
 start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'
connect by prior t.dept_id = t.par_dept_id
 order by level, t.dept_code;
           

備注：在他人部落格基礎上進行的改進和分析。