Oracle樹查詢的最重要的就是selectstart with connect by prior 語法了依托於該語法我們可以將一個表形結構的中以樹的順序列出來在下面列述了Oracle中樹型查詢的常用查詢方式以及經常使用的與樹查詢相關的Oracle特性函數等在這裡只涉及到一張表中的樹查詢方式而不涉及多表中的關聯等
以我做過的一個項目中的表為例表結構如下
Sql代碼
CREATE TABLE FLFL
(
ID NUMBER NOT NULL
MC NVARCHAR()
FLJB NUMBER
SJFLID NUMBER
)
FLJB是作為樹的級別在很多查詢中可以加快SQL的查詢效率在下面演示的功能基本上不使用這個關鍵字
SJFLID存儲的是上級ID如果是頂級父節點該SJFLID為null（得補充一句當初的確是這樣設計的不過現在知道表中最好別有null記錄這會引起全文掃描建議改成代替)
我們從最基本的操作逐步列出樹查詢中常見的操作所以查詢出來的節點以家族中的輩份作比方
查找樹中的所有頂級父節點（輩份最長的人） 假設這個樹是個目錄結構那麼第一個操作總是找出所有的頂級節點再根據該節點找到其下屬節點
Sql代碼
SELECT * FROM flfl WHERE sjflid IS NULL;
這是個引子沒用到樹型查詢
查找一個節點的直屬子節點（所有兒子） 如果查找的是直屬子類節點也是不用用到樹型查詢的
Sql代碼
SELECT * FROM flfl WHERE sjflid = ;
這個可以找到ID為的直屬子類節點
查找一個節點的所有 直屬子節點（所有後代）
Sql代碼
SELECT * FROM flfl START WITH ID = CONNECT BY sjflid = PRIOR ID;
這個查找的是ID為的節點下的所有直屬子類節點包括子輩的和孫子輩的所有直屬節點
查找一個節點的直屬父節點（父親） 如果查找的是節點的直屬父節點也是不用用到樹型查詢的
Sql代碼
SELECT b* FROM flfl a JOIN flfl b ON asjflid = bID WHERE aID = ;
這個找到的是ID為的節點的直屬父節點要用到同一張表的關聯了
查找一個節點的所有直屬父節點（祖宗）
Sql代碼
SELECT * FROM flfl START WITH ID = CONNECT BY PRIOR sjflid = ID;
這裡查找的就是ID為的所有直屬父節點打個比方就是找到一個人的父親祖父等但是值得注意的是這個查詢出來的結果的順序是先列出子類節點再列出父類節點姑且認為是個倒序吧
上面列出兩個樹型查詢方式第條語句和第條語句這兩條語句之間的區別在於prior關鍵字的位置不同所以決定了查詢的方式不同 當sjflid = PRIOR ID時數據庫會根據當前的ID迭代出sjflid與該ID相同的記錄所以查詢的結果是迭代出了所有的子類記錄而PRIOR ID = sjflid時數據庫會跟據當前的sjflid來迭代出與當前的sjflid相同的id的記錄所以查詢出來的結果就是所有的父類結果
以下是一系列針對樹結構的更深層次的查詢這裡的查詢不一定是最優的查詢方式或許只是其中的一種實現而已
查詢一個節點的兄弟節點（親兄弟）
Sql代碼
SELECT a*
FROM flfl a
WHERE EXISTS (SELECT *
FROM flfl b
WHERE asjflid = bsjflid AND bID = );
這裡查詢的就是與ID為的節點同屬一個父節點的節點了就好比親兄弟了
查詢與一個節點同級的節點（族兄弟） 如果在表中設置了級別的字段上表中的FLJB那麼在做這類查詢時會很輕松同一級別的就是與那個節點同級的在這裡列出不使用該字段時的實現!
Sql代碼
WITH tmp AS
(SELECT a* LEVEL lev
FROM flfl a
START WITH asjflid IS NULL
CONNECT BY asjflid = PRIOR aID)
SELECT *
FROM tmp
WHERE lev = (SELECT lev
FROM tmp
WHERE ID = )
這裡使用兩個技巧一個是使用了LEVEL來標識每個節點在表中的級別還有就是使用with語法模擬出了一張帶有級別的臨時表
查詢一個節點的父節點的的兄弟節點（伯父與叔父）
Sql代碼
WITH tmp AS
(SELECT flfl* LEVEL lev
FROM flfl
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID)
SELECT b*
FROM tmp b
(SELECT *
FROM tmp
WHERE ID = AND lev = ) a
WHERE blev =
UNION ALL
SELECT *
FROM tmp
WHERE sjflid = (SELECT DISTINCT xID
FROM tmp x
tmp y
(SELECT *
FROM tmp
WHERE ID = AND lev > ) z
WHERE yID = zsjflid AND xID = ysjflid);
這裡查詢分成以下幾步首先將第個一樣將全表都使用臨時表加上級別其次根據級別來判斷有幾種類型以上文中舉的例子來說有三種情況（）當前節點為頂級節點即查詢出來的lev值為那麼它沒有上級節點不予考慮（）當前節點為級節點查詢出來的lev值為那麼就只要保證lev級別為的就是其上級節點的兄弟節點（）其它情況就是以及以上級別那麼就要選查詢出來其上級的上級節點（祖父）再來判斷祖父的下級節點都是屬於該節點的上級節點的兄弟節點 最後就是使用UNION將查詢出來的結果進行結合起來形成結果集
查詢一個節點的父節點的同級節點（族叔）
這個其實跟第種情況是相同的
Sql代碼
WITH tmp AS
(SELECT a* LEVEL lev
FROM flfl a
START WITH asjflid IS NULL
CONNECT BY asjflid = PRIOR aID)
SELECT *
FROM tmp
WHERE lev = (SELECT lev
FROM tmp
WHERE ID = )
只需要做個級別判斷就成了
基本上常見的查詢在裡面了不常見的也有部分了其中查詢的內容都是節點的基本信息都是數據表中的基本字段但是在樹查詢中還有些特殊需求是對查詢數據進行了處理的常見的包括列出樹路徑等
補充一個概念對於數據庫來說根節點並不一定是在數據庫中設計的頂級節點對於數據庫來說根節點就是start with開始的地方
下面列出的是一些與樹相關的特殊需求
名稱要列出名稱全部路徑
這裡常見的有兩種情況一種是是從頂級列出直到當前節點的名稱（或者其它屬性）一種是從當前節點列出直到頂級節點的名稱（或其它屬性）舉地址為例國內的習慣是從省開始到市到縣到居委會的而國外的習慣正好相反（老師說的還沒接過國外的郵件誰能寄個瞅瞅 ）
從頂部開始
Sql代碼
SELECT SYS_CONNECT_BY_PATH (mc /)
FROM flfl
WHERE ID =
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID;
從當前節點開始
Sql代碼
SELECT SYS_CONNECT_BY_PATH (mc /)
FROM flfl
START WITH ID =
CONNECT BY PRIOR sjflid = ID;
在這裡我又不得不放個牢騷了oracle只提供了一個sys_connect_by_path函數卻忘了字符串的連接的順序在上面的例子中第一個SQL是從根節點開始遍歷而第二個SQL是直接找到當前節點從效率上來說已經是千差萬別更關鍵的是第一個SQL只能選擇一個節點而第二個SQL卻是遍歷出了一顆樹來再次PS一下
sys_connect_by_path函數就是從start with開始的地方開始遍歷並記下其遍歷到的節點start with開始的地方被視為根節點將遍歷到的路徑根據函數中的分隔符組成一個新的字符串這個功能還是很強大的
列出當前節點的根節點
在前面說過根節點就是start with開始的地方
Sql代碼
SELECT CONNECT_BY_ROOT mc flfl*
FROM flfl
START WITH ID =
CONNECT BY PRIOR sjflid = ID;
connect_by_root函數用來列的前面記錄的是當前節點的根節點的內容
列出當前節點是否為葉子
這個比較常見尤其在動態目錄中在查出的內容是否還有下級節點時這個函數是很適用的
Sql代碼
SELECT CONNECT_BY_ISLEAF flfl*
FROM flfl
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID;
connect_by_isleaf函數用來判斷當前節點是否包含下級節點如果包含的話說明不是葉子節點這裡返回反之如果不包含下級節點這裡返回
至此oracle樹型查詢基本上講完了以上的例子中的數據是使用到做過的項目中的數據因為裡面的內容可能不好理解所以就全部用一些新的例子來進行闡述以上所有SQL都在本機上測試通過也都能實現相應的功能但是並不能保證是解決這類問題的最優方案（如第條明顯寫成存儲過程會更好）如果誰有更好的解決方案或者有關oracle樹查詢的任何問題歡迎留言討論以上的SQL有什麼問題也歡迎大家留言批評
From:http://tw.wingwit.com/Article/program/Oracle/201311/11193.html
