Oracle--樹形自關聯表查詢SQL

標籤：必須   結果   基本用法   開始   不用   檢索   rom   start   兄弟節點   

平時會經常用到自關聯的樹形結構表來儲存樹形結構的資料，資料之間的層次聯絡代表著樹形結構中的父子關係，並通常用表中兩個列間的聯絡來描述，如下表中ID 和 PARENT_ID，ID為家族成員編號，PARENT_ID為父母編號，從而可以形成一個樹形結構的家族關係。

 

一、基本用法

基本語句：select ··· from ··· connect by prior ··· start with ··· order siblings by ···

如家族樹的表查詢語句為：

select * from family_tree connect by prior  id = parent_id  start with  appellation = ‘爺爺‘ order siblings by  age  desc

查詢結果如下：（從‘爺爺’開始往子孫後代遍曆查詢，並且兄弟節點間按年齡由大到小排序）

 

二、connect by & where  剪枝和去點

connect by 表明每行資料將是按層次順序檢索，並規定了按照什麼規則將資料進行關聯。

不僅如此connect by 還可以限制查詢的分支，通過限制條件進行剪枝：還是之前的查詢，但這次要求不查詢 ‘小姑‘ 及其子女

select * from family_tree connect by prior  id = parent_id  and appellation <> ‘小姑‘ start with  appellation = ‘爺爺‘ order siblings by  age  desc

查詢結果如下：還是之前的查詢，但這次要求不查詢 ‘小姑‘ 及其子女

 

若只想不查詢 ‘小姑‘ 但其子女還是需要查詢的，則需要使用 where 進行限制，也就是只去除單個或多個節點：

select * from family_tree where appellation <> ‘小姑‘ connect by prior  id = parent_id  start with  appellation = ‘爺爺‘ order siblings by  age  desc

查詢結果如下：‘小姑‘ 節點被去掉了，而其子女節點仍然還在。

 

 

where 子句不能在 connect by 子句的後面，否則會報錯。

 

三、關於prior

prior需要和connect by 一起使用，且必須放置在 連結關係的兩列中某一列的前面，用來標記哪一列是 ‘子標識‘。從而決定了檢索的順序。

若prior放在 ‘子標識‘ 的前面則檢索順序是按照由上自下（由根到葉）的順序檢索。（這時SQL語句標識到了正確的 ‘子標識‘）

若prior放在 ‘父標識‘ 的前面則檢索順序是按照自下而上（由葉到根）的順序檢索。（這時SQL語句標識到了錯誤的 ‘子標識‘，並將 ‘父標識‘ 錯認為了 ‘子標識‘ 所以檢索方向正好相反了）

如，下面的語句，將prior 放在 parent_id（父標識） 前，則檢索方向就是子孫向爺爺檢索。

select * from family_tree connect by id = prior parent_id  start with  appellation = ‘表弟‘ order siblings by  age  desc

查詢結果如下：

 

四、start with 定義尋找起始節點

start with 子句規定了從哪個或哪些節點開始檢索樹。若start with 子句被省略了，則所有的節點都是起始節點。

如，下面的語句就只查詢以 ‘大伯‘ 和 ‘小姑‘ 為起始節點的分支。

select * from family_tree connect by prior id = parent_id  start with  appellation = ‘大伯‘ or appellation = ‘小姑‘ order siblings by  age  desc

查詢結果如下：

 

五、order siblings by 同層級的兄弟節點間的排序

order siblings by 顧名思義是同層級的兄弟節點間的排序，上面的語句中有都用到了這樣的排序。

select * from family_tree connect by prior  id = parent_id  start with  appellation = ‘爺爺‘ order siblings by age asc

查詢結果：

同樣的，order by 子句也是可以應用的，但是所有節點一起排序，這樣會打亂樹形結構

select * from family_tree connect by prior  id = parent_id  start with  appellation = ‘爺爺‘ order by age asc

 

六、偽列 level

level 需要和connect by 子句一起使用表示節點在樹結構中的層級，根節點為 1，根節點的子節點為 2，依次類推。這裡的根節點是 start with 限制的起始節點。

如下語句：

select level, T.* from family_tree T connect by prior  id = parent_id  start with  appellation = ‘爺爺‘ order by age desc

查詢結果如下，包括了 level欄位

 

七、子句執行順序：from --> start with --> connect by --> where --> select --> order by

PS：

1、where 是在 connect by 後執行的（雖然寫法是需要寫在前面），所以 where 才有只去節點不剪分支（在樹形關係結構都查詢出來後，才通過 where 進行限制篩選）。

2、order by 是最後才執行，所以使用order by 不用 order siblings by 會打亂已有的樹形結構檢索，只按 order by 子句排序。

 

8、前序走訪

樹形查詢子句是遞迴處理過程，樹的根節點向分葉節點遞迴查詢時，遍曆順序是按照樹的前序走訪進行的。

Oracle--樹形自關聯表查詢SQL