章節目錄
-
- 樹形資料示例
- Connect By
- Start With
- Level & Path
- 檢測異常節點
-
- 循環依賴
- 節點丢失
- Where條件過濾
樹形資料示例
日常工作中，經常遇到樹形結構的資料，即每條記錄（或節點）都存在一個 Parent_ID字段，用于辨別其父節點的ID。例如：
with node_list as (
select 1 id, null parent_id, 'node1' name from dual union all
select 2 id, 1 parent_id, 'node1-2' name from dual union all
select 3 id, 1 parent_id, 'node1-3' name from dual union all
select 4 id, 2 parent_id, 'node1-2-4' name from dual union all
select 5 id, 2 parent_id, 'node1-2-5' name from dual union all
select 6 id, 5 parent_id, 'node1-2-5-6' name from dual union all
select 7 id, 0 parent_id, 'node0-7' name from dual union all
select 8 id, 9 parent_id, 'node9-8' name from dual union all
select 9 id, 8 parent_id, 'node8-9' name from dual
)
select * from node_list t;
注意： 以下SQL直接采用 臨時表 node_list，省略 with node_list as… 子句。
查詢結果：
對于此類資料，我們經常需要明确目前節點所在樹的位置、樹的根在哪裡、目前節點是葉子還是分叉？
Connect By
如果希望還原樹形結構的原貌，可以通過Connect By 建立前後記錄的父子關系：
select * from node_list t
connect by prior t.id = t.parent_id;
但是，在本文例子中，存在兩個特殊節點：node9-8 和 node8-9，這兩個節點互相成為對方的Parent，這種情況屬于循環依賴。如果直接查詢，将會報錯：
此時，應通過 NOCYCLE 關鍵字，避免循環依賴報錯。
select * from node_list t
connect by NOCYCLE prior t.id = t.parent_id;
查詢結果：
Start With
通過Start With，我們可以限定查詢樹的起始節點。比如，可以從 node1開始查詢：
select * from node_list t
start with t.parent_id is null
connect by NOCYCLE prior t.id = t.parent_id;
查詢結果：
這樣就能看到目前節點及其向下的所有子節點。
Level & Path
既然是樹狀結構，那麼每個節點都有對應的級别（Level 直譯），如目前為1，則其子、孫節點的級别分别為2、3，此處Level 或者翻譯成輩份、代級更合适。例如：
select t.*, level from node_list t
start with t.id = 2
connect by NOCYCLE prior t.id = t.parent_id;
查詢結果：
為了避免混淆，我們在給節點name命名時，刻意展現了其父子關系。但是樹狀結構資料本身可以通過sys_connect_by_path 函數來展現這種關系：
select t.*, level, sys_connect_by_path(t.id, '/') curr_path from node_list t
start with t.id = 1
connect by NOCYCLE prior t.id = t.parent_id;
查詢結果：
由上可見，每個節點所在樹的完整路徑。
其他關鍵字：
- CONNECT_BY_ISLEAF：判斷目前節點是否為葉子節點（即無子嗣的、無後續節點的）；
- CONNECT_BY_ISCYCLE：檢查目前節點是否存在上下層互為父子節點，即循環依賴的情況，詳見循環依賴章節；
- CONNECT_BY_ROOT：與某個字段搭配，以擷取根節點的對應字段資訊。
舉例：
select t.*, connect_by_isleaf, connect_by_root(name)
from node_list t
start with t.id = 1
connect by NOCYCLE prior t.id = t.parent_id;
查詢結果：
綜合運用這些關鍵字，可以幫助我們處理樹狀結構資料，實作相應的業務功能。
檢測異常節點
循環依賴
本文例子中的資料 node9-8和node8-9，是一個循環依賴的例子：
select t.*, connect_by_iscycle
from node_list t
start with t.id = 8
connect by NOCYCLE prior t.id = t.parent_id;
查詢結果：
通過這種方式，可以檢查和過濾存在循環依賴的節點，避免程式加載資料時出錯。
節點丢失
在根節點上，我們預設其parent_id 為空，但是在誤删資料等情況，可能導緻根節點丢失。比如本文例子中的 node0-7，其parent_id為0，但id為0的記錄是不存在的。這種情況，将可能導緻該節點被丢掉。
例如：
select t.* from node_list t
start with t.parent_id is null
connect by NOCYCLE prior t.id = t.parent_id;
查詢結果：
可以看到，在結果中，并不存在 node0-7節點。
Where條件過濾
結合 Where條件，我們可以從樹狀結構的表中提取出我們想要的樹。
select t.*
from node_list t
where t.name like 'node%'
start with t.parent_id is null
connect by NOCYCLE prior t.id = t.parent_id;
對于儲存了大量事實資料、業務單據号的表來說，Where條件是不可缺少的。