樹概念及使用connect by進行級聯查詢

樹

樹，大家都見過，以這種形式的數據關係，就是樹。下麵看一張圖，瞭解什麼是根節點（樹幹）、節點或分叉、葉（葉節點）

connect by 級聯查詢

connect by可以用於級聯查詢，常用於對具有樹狀結構的記錄查詢某一節點的所有子孫節點或所有祖輩節點。

來看一個示例，現假設我們擁有一個菜單表t_menu，其中只有三個欄位：id、name和parent_id。它們是具有父子關係的，最頂級的菜單對應的parent_id為0。現假設我們擁有如下記錄：

       如果這個時候我們需要查詢“菜單01”以及其下所有的子孫菜單應該怎麼辦呢？如果使用connect by的話這將會非常簡單，使用如下SQL語句就可以達到對應的效果。

       connect by是需要跟start with一起使用的。connect by後跟的是連接條件，在connect by後接的條件通常都需要使用關鍵字“prior”，可以簡單的把它理解為上一級，所以上述例子中“connect by parent_id=prior id”就表示連接條件為parent_id等於上級的id，查找到下一級記錄後又會找parent_id等於下一級記錄的id的記錄，而prior對應的最頂層的記錄就是通過start with來確定的，start with後接對應的篩選條件，表示最頂層的記錄是哪些，最頂層的記錄可以有多個，比如我想查找“菜單01”下的子孫菜單，但是不包括“菜單01”本身，那麼我就可以使用如下的SQL語句進行查找，此時“start with parent_id=1”對應的記錄就會有多條。

       對應的結果為：

       此外，如果我們想查找“菜單010101”對應的祖輩菜單也非常簡單，如下SQL就可以實現該功能，即從“菜單010101”的父菜單（對應id為4）開始查找。

       對應的結果為：

level

       使用connect by時我們可以使用內置的類似於rownum的一個叫level的偽列，該列表示當前記錄相對於start with記錄的一個層級，start with記錄的level為1。如上面的兩條SQL語句，如果加上level的話對應的結果將是這樣的。

       對應的結果為：

       對應的結果為：

       有了level後，我們就可以對查詢的level做一個限制，比如只查從最頂層開始向下兩級的菜單。

       從上述SQL我們可以看到where條件是直接跟在from之後的，使用connect by時我們的where條件不是在connect by之前對數據進行過濾的，而是在connect by之後才對所有的數據進行過濾的，這一點跟使用分組語句group by時是不一樣的，group by是先通過where對需要分組的數據進行過濾後再通過group by來分組的。

nocycle和connect_by_iscycle

       如果我們的記錄中存在迴圈的父子關係，則使用connect by進行查詢時會拋出異常，如A->B、B->C、C->A這樣的記錄。解決辦法是在connect by語句後加上“nocycle”，表示不迴圈查詢，如：

       使用nocycle後對於A->B、B->C、C->A這樣的記錄會通過查詢B，然後通過B查詢C，再通過C查詢A時發現已經迴圈了，就不再查詢了，即在C這條記錄這裡迴圈了。在對存在迴圈記錄的查詢中我們也可以通過“connect_by_iscycle”找到是哪一條記錄迴圈了，“connect_by_iscycle”也是一個偽列，其必須和nocycle一起使用。偽列“connect_by_iscycle”對應的值有0和1，如果某一條記錄的connect_by_iscycle對應的值為1則表示從該條記錄這裡開始迴圈了。如下是一個使用connect_by_iscycle的示例。

connect_by_isleaf

       connect_by_isleaf也是一個偽列，其表示對應的記錄是否是一個葉子節點，即在進行connect by時不能通過該記錄找到下一條記錄。其對應的值有0和1,0表示非葉子節點，1表示是葉子節點。如我只想找出是葉子節點的菜單時對應的SQL可以這樣寫：

connect_by_root

       connect_by_root表示根節點，即某一條記錄所對應的最頂級的記錄，其用法跟prior類似，後面也需要跟一個欄位名。如下麵示例可以查詢所有葉子節點菜單的最頂級菜單和上級菜單的名稱。

select connect_by_root name as root_name, prior name as prior_name,t.* from t_menu t where connect_by_isleaf=1 connect by prior id=parent_id start with parent_id=0;  

       對應上表的記錄，在上述SQL中查詢出來的結果應該如下所示：

sys_connect_by_path

       sys_connect_by_path(column,delimiter)可以用來展示以指定column和分隔符delimiter表示從根節點到當前節點的路徑。以下SQL用來查詢id為2的菜單下葉子節點的信息，包括以欄位name和分隔符“>”表示的其對應的根節點的路徑。

       對應結果如下所示：

排序order

       可以使用order by對connect by之後的結果進行排序，此時order by需放在最末端，而不像where篩選那樣直接定義在from之後。如需對connect by之後的結果按id進行排序，則可以使用如下SQL語句：

       除了傳統的針對查詢結果的排序外，connect by語句還支持對同一父節點下的子節點進行排序，這是通過order siblings by來定義的。如我們需要查詢id為2的菜單下的所有子孫菜單，然後對具有同一父節點的菜單按id進行倒序排列，則我們的SQL語句可以如下定義：

       對應的結果會是這樣子：

       如上表所示，我們可以看到“菜單0201”和“菜單0202”具有相同的父節點“菜單02”，它們按照id進行倒序排列，所有“菜單0202”在“菜單0201”之前，同樣“菜單020101”、“菜單020102”和“菜單020103”具有相同的父節點“菜單0201”，所以它們也是按照id的倒序排列。

一次針對connect by的查詢優化

       有這麼一個需求：表A表示分類，表B表示任務模板，A與B是一對多的關係，每一個任務模板都屬於一個特定的分類，在表B中用欄位a表示所屬的分類。分類存在父子關係，子分類的parent_id對應父分類的id。現假設需要統計id為1的分類及其子分類下存在的任務模板數量。對應SQL如下：

       現假設擁有另外一個表C，其表示任務實例，一個任務模板B可以擁有n個任務實例B，即B跟C之間是一對多的關係。任務實例C通過欄位b關聯任務模板B，另外任務實例C擁有一個欄位status表示任務實例的具體狀態。現假設需要統計id為1的分類及其子分類下各狀態的任務實例數量。對應SQL如下：

       在A表數據量1000，B表數據量20000，C表數據量5000，id為1的分類下屬的子孫分類數量為100的情況下第一條SQL的查詢速度可以在0.1秒左右完成，而第二條SQL需要將近10秒才能完成。把查詢id為1的分類下子孫分類的id的SQL語句“selectidfrom A connectbypriorid=parent_id startwithid=1”單獨查詢的速度也可以在0.1秒內完成。通常對於這種數量級別的三表查詢都是可以在0.1秒內完成的，為此心想第二條SQL應該是受了子查詢中connect by的影響。後來決定把分類的子查詢直接作為B的in條件進行查詢，如下所示：

       其查詢效果是一樣的，心想應該還是connect by影響到了，既然單獨使用connect by查詢id為1的分類的子孫分類的id只需要不到0.1秒，那何不在程式裡面先將id為1的分類的子孫分類id查詢出來，再作為B、C聯合查詢的in條件，如：

       結果查詢結果也可以在0.1秒內完成。

思考

假設需要求菜單parentId為16的數據到菜單03的路徑，用下麵代碼

       select *,sys_connect_by_path(name,'/') from t_menu where parent_id = 16 start with  id is not null connect by  prior id = parent_id

       得出的是每個節點往下的路徑。

　　把prior 放在parent_id 前面，可以得出全路徑，但是不好提取到根的路徑。而且如果資料庫很大， start with 用 is not null  會很慢。

　　如果這樣操作：

select * from t_menu t
left join (
select connect_by_root id as id ,sys_connect_by_path(name,'/') path
from t_menu
where id=3
start with (select id from t_menu where parent_id =16)
connect by id =prior parent_id) t1
on t.id=t1.id

用connect_by_root 來取出path的actul id，再和原表左聯。會不會好一點？