Oracle樹查詢及函數

標籤：

      Oracle樹查詢的最重要的就是select...start with... connect by...prior文法了。依託於該文法，我們可以將一個表形結構的中以樹的順序列出來。在下面列述了Oracle中樹型查詢的常用查詢方式以及經常使用的與樹查詢相關的Oracle特性函數等，在這裡只涉及到一張表中的樹查詢方式而不涉及多表中的關聯等。

      以我做過的一個項目中的表為例，表結構如下：

Sql代碼  CREATE TABLE FLFL  
(  
  ID      NUMBER                                NOT NULL,  
  MC      NVARCHAR2(20),  
  FLJB    NUMBER,  
  SJFLID  NUMBER  
)  

      FLJB是作為樹的層級，在很多查詢中可以加快SQL的查詢效率。在下面示範的功能基本上不使用這個關鍵字。

      SJFLID儲存的是上級ID，如果是頂級父節點，該SJFLID為null（得補充一句，當初的確是這樣設計的，不過現在知道，表中最好別有null記錄，這會引起全文掃描，建議改成0代替)。

      我們從最基本的操作，逐步列出樹查詢中常見的操作，所以查詢出來的節點以家族中的輩份作比方。

 

      1. 尋找樹中的所有頂級父節點（輩份最長的人）。假設這個樹是個目錄結構，那麼第一個操作總是找出所有的頂級節點，再根據該節點找到其下屬節點。

Sql代碼  SELECT * FROM flfl WHERE sjflid IS NULL;  

      這是個引子，沒用到樹型查詢。

 

      2.尋找一個節點的直屬子節點（所有兒子）。如果尋找的是直屬子類節點，也是不用用到樹型查詢的。

Sql代碼  SELECT * FROM flfl WHERE sjflid = 819459;  

      這個可以找到ID為819459的直屬子類節點。

 

      3.尋找一個節點的所有直屬子節點（所有後代）。

Sql代碼  SELECT * FROM flfl START WITH ID = 819459 CONNECT BY sjflid = PRIOR ID;  

      這個尋找的是ID為819459的節點下的所有直屬子類節點，包括子輩的和孫子輩的所有直屬節點。

 

      4.尋找一個節點的直屬父節點（父親）。如果尋找的是節點的直屬父節點，也是不用用到樹型查詢的。

Sql代碼  SELECT b.* FROM flfl a JOIN flfl b ON a.sjflid = b.ID WHERE a.ID = 6758;  

      這個找到的是ID為6758的節點的直屬父節點，要用到同一張表的關聯了。

 

      5.尋找一個節點的所有直屬父節點（祖宗）。

Sql代碼  SELECT * FROM flfl START WITH ID = 6758 CONNECT BY PRIOR sjflid = ID;  

      這裡尋找的就是ID為6758的所有直屬父節點，打個比方就是找到一個人的父親、祖父等。但是值得注意的是這個查詢出來的結果的順序是先列出子類節點再列出父類節點，姑且認為是個倒序吧。

 

      上面列出兩個樹型查詢方式，第3條語句和第5條語句，這兩條語句之間的區別在於prior關鍵字的位置不同，所以決定了查詢的方式不同。當sjflid = PRIORID時，資料庫會根據當前的ID迭代出sjflid與該ID相同的記錄，所以查詢的結果是迭代出了所有的子類記錄；而PRIOR ID =sjflid時，資料庫會跟據當前的sjflid來迭代出與當前的sjflid相同的id的記錄，所以查詢出來的結果就是所有的父類結果。

      以下是一系列針對樹結構的更深層次的查詢，這裡的查詢不一定是最優的查詢方式，或許只是其中的一種實現而已。

 

      6.查詢一個節點的兄弟節點（親兄弟）。

Sql代碼  SELECT a.*  
  FROM flfl a  
 WHERE EXISTS (SELECT *  
                 FROM flfl b  
                WHERE a.sjflid = b.sjflid AND b.ID = 6757);  

      這裡查詢的就是與ID為6757的節點同屬一個父節點的節點了，就好比親兄弟了。

 

      7.查詢與一個節點同級的節點（族兄弟）。如果在表中設定了層級的欄位，上表中的FLJB，那麼在做這類查詢時會很輕鬆，同一層級的就是與那個節點同級的，在這裡列出不使用該欄位時的實現!

Sql代碼  WITH tmp AS  
     (SELECT     a.*, LEVEL lev  
            FROM flfl a  
      START WITH a.sjflid IS NULL  
      CONNECT BY a.sjflid = PRIOR a.ID)  
SELECT *  
  FROM tmp  
 WHERE lev = (SELECT lev  
                FROM tmp  
               WHERE ID = 819394)  

       這裡使用兩個技巧，一個是使用了LEVEL來標識每個節點在表中的層級，還有就是使用with文法類比出了一張帶有層級的暫存資料表。

 

      8.查詢一個節點的父節點的的兄弟節點（伯父與叔父）。

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 = 7004 AND lev = 2) a  
 WHERE b.lev = 1  
UNION ALL  
SELECT *  
  FROM tmp  
 WHERE sjflid = (SELECT DISTINCT x.ID  
                            FROM tmp x,  
                                 tmp y,  
                                 (SELECT *  
                                    FROM tmp  
                                   WHERE ID = 7004 AND lev > 2) z  
                           WHERE y.ID = z.sjflid AND x.ID = y.sjflid);  

       這裡查詢分成以下幾步。首先，將第7個一樣，將全表都使用暫存資料表加上層級；其次，根據層級來判斷有幾種類型，以上文中舉的例子來說，有三種情況：（1）當前節點為頂級節點，即查詢出來的lev值為1，那麼它沒有上級節點，不予考慮。（2）當前節點為2級節點，查詢出來的lev值為2，那麼就只要保證lev層級為1的就是其上級節點的兄弟節點。（3）其它情況就是3以及以上層級，那麼就要選查詢出來其上級的上級節點（祖父），再來判斷祖父的下級節點都是屬於該節點的上級節點的兄弟節點。最後，就是使用UNION將查詢出來的結果進行結合起來，形成結果集。

 

      9.查詢一個節點的父節點的同級節點（族叔）。

      這個其實跟第7種情況是相同的。

Sql代碼  WITH tmp AS  
     (SELECT     a.*, LEVEL lev  
            FROM flfl a  
      START WITH a.sjflid IS NULL  
      CONNECT BY a.sjflid = PRIOR a.ID)  
SELECT *  
  FROM tmp  
 WHERE lev = (SELECT lev  
                FROM tmp  
               WHERE ID = 819394) - 1  

      只需要做個層級判斷就成了。

 

      基本上，常見的查詢在裡面了，不常見的也有部分了。其中，查詢的內容都是節點的基本資料，都是資料表中的基本欄位，但是在樹查詢中還有些特殊需求，是對查詢資料進行了處理的，常見的包括列出樹路徑等。

      補充一個概念，對於資料庫來說，根節點並不一定是在資料庫中設計的頂級節點，對於資料庫來說，根節點就是start with開始的地方。

      下面列出的是一些與樹相關的特殊需求。

 

      10.名稱要列出名稱全部路徑。

      這裡常見的有兩種情況，一種是是從頂級列出，直到當前節點的名稱（或者其它屬性）；一種是從當前節點列出，直到頂級節點的名稱（或其它屬性）。舉地址為例：國內的習慣是從省開始、到市、到縣、到居委會的，而國外的習慣正好相反（老師說的，還沒接過國外的郵件，誰能寄個瞅瞅）。

      從頂部開始：

Sql代碼  SELECT     SYS_CONNECT_BY_PATH (mc, ‘/‘)  
      FROM flfl  
     WHERE ID = 6498  
START WITH sjflid IS NULL  
CONNECT BY sjflid = PRIOR ID;  

      從當前節點開始：

Sql代碼  SELECT     SYS_CONNECT_BY_PATH (mc, ‘/‘)  
      FROM flfl  
START WITH ID = 6498  
CONNECT BY PRIOR sjflid = ID;  

      在這裡我又不得不放個牢騷了。oracle只提供了一個sys_connect_by_path函數，卻忘了字串的串連的順序。在上面的例子中，第一個SQL是從根節點開始遍曆，而第二個SQL是直接找到當前節點，從效率上來說已經是千差萬別，更關鍵的是第一個SQL只能選擇一個節點，而第二個SQL卻是遍曆出了一顆樹來。再次PS一下。

      sys_connect_by_path函數就是從start with開始的地方開始遍曆，並記下其遍曆到的節點，start with開始的地方被視為根節點，將遍曆到的路徑根據函數中的分隔字元，組成一個新的字串，這個功能還是很強大的。

 

      11.列出當前節點的根節點。

      在前面說過，根節點就是start with開始的地方。

Sql代碼  SELECT     CONNECT_BY_ROOT mc, flfl.*  
      FROM flfl  
START WITH ID = 6498  
CONNECT BY PRIOR sjflid = ID;  

      connect_by_root函數用來列的前面，記錄的是當前節點的根節點的內容。

 

      12.列出當前節點是否為葉子。

      這個比較常見，尤其在動態目錄中，在查出的內容是否還有下級節點時，這個函數是很適用的。

Sql代碼  SELECT     CONNECT_BY_ISLEAF, flfl.*  
      FROM flfl  
START WITH sjflid IS NULL  
CONNECT BY sjflid = PRIOR ID;  

      connect_by_isleaf函數用來判斷當前節點是否包含下級節點，如果包含的話，說明不是葉子節點，這裡返回0；反之，如果不包含下級節點，這裡返回1。

 

      至此，oracle樹型查詢基本上講完了，以上的例子中的資料是使用到做過的項目中的資料，因為裡面的內容可能不好理解，所以就全部用一些新的例子來進行闡述。以上所有SQL都在本機上測試通過，也都能實現相應的功能，但是並不能保證是解決這類問題的最優方案（如第8條明顯寫成預存程序會更好），如果誰有更好的解決方案、或者有關oracle樹查詢的任何問題，歡迎留言討論，以上的SQL有什麼問題也歡迎大家留言批評。

Oracle樹查詢及函數