\(^_^)/ Oracle 樹查詢

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

oracle樹查詢又稱為遞歸查詢，是最常用的查詢方法之一：

select * from tablename 
       start with cond1 
       connect by cond2 
       where cond3;

簡單說來是将一個樹狀結構存儲在一張表裡，比如一個表中存在兩個字段:

id,parentid那麼通過表示每一條記錄的parent是誰，就可以形成一個樹狀結構。

用上述文法的查詢可以取得這棵樹的所有記錄。

其中COND1是根結點的限定語句，當然可以放寬限定條件，以取得多個根結點，實際就是多棵樹。

COND2是連接配接條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR ID=PRAENTID就是說上一條記錄的ID是本條記錄的PRAENTID，即本記錄的父親是上一條記錄。

COND3是過濾條件，用于對傳回的所有記錄進行過濾。

Oracle中的select語句可以用START WITH...CONNECT BY PRIOR子句實作遞歸查詢，connect by 是結構化查詢中用到的，其基本文法是：

查詢語句

--由子節點向根節點追溯

select *
  from t_address t
 start with t.address_name = '北京市'
connect by prior t.parent_id = t.id
 order by t.id asc;

--由根查詢所有子節點

select *
  from t_address t
 start with t.address_name = '天津市'
connect by t.parent_id = prior t.id

--由關鍵字address_level查詢所在層次

select t.*, t.address_level, level
  from t_address t
 start with t.address_name = '海澱區'
connect by prior t.parent_id = t.id

舉例，表結構如下：

CREATE TABLE FLFL   
    (   
         ID       NUMBER                                NOT NULL,   --Id
         MC       NVARCHAR2(20),    --名稱
         FLJB     NUMBER,    --級别
         SJFLID   NUMBER   --上級Id
   )

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

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

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

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

SELECT * FROM flfl WHERE sjflid IS NULL;

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

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

SELECT * FROM flfl WHERE sjflid = 819459;

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

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

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

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

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

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

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

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

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

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

上面列出兩個樹型查詢方式，第3條語句和第5條語句，這兩條語句之間的差別在于prior關鍵字的位置不同，是以決定了查詢的方式不同。

當sjflid = PRIOR ID時，資料庫會根據目前的ID疊代出sjflid與該ID相同的記錄，是以查詢的結果是疊代出了所有的子類記錄；而PRIOR ID = sjflid時，資料庫會跟據目前的sjflid來疊代出與目前的sjflid相同的id的記錄，是以查詢出來的結果就是所有的父類結果。

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

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

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

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

7.查詢與一個節點同級的節點（族兄弟）。

如果在表中設定了級别的字段，上表中的FLJB，那麼在做這類查詢時會很輕松，同一級别的就是與那個節點同級的，在這裡列出不使用該字段時的實作!

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.查詢一個節點的父節點的的兄弟節點（伯父與叔父）。

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 &gt;
    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種情況是相同的。

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.名稱要列出名稱全部路徑。

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

從頂部開始：

SELECT SYS_CONNECT_BY_PATH(mc, '/')
    FROM flfl
   WHERE ID = 6498
   START WITH sjflid IS NULL
  CONNECT BY sjflid = PRIOR ID;
  SELECT SYS_CONNECT_BY_PATH(swjg_mc, '-')
    FROM dim_swjg
   WHERE swjg_id = '13306810000'
   START WITH swjg_sj_id = '00000000000'
  CONNECT BY swjg_sj_id = PRIOR swjg_id;

從目前節點開始：

SELECT SYS_CONNECT_BY_PATH(mc, '/')
    FROM flfl
   START WITH ID = 6498
  CONNECT BY PRIOR sjflid = ID;
  SELECT SYS_CONNECT_BY_PATH(swjg_mc, '-')
    FROM dim_swjg
   START WITH swjg_id = '13306810000'
  CONNECT BY PRIOR swjg_sj_id = swjg_id;

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

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

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

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

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

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

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

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

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條明顯寫成存儲過程會更好）

執行個體：

SELECT * FROM dim_swjg t WHERE t.swjg_sj_id = '00000000000' ; /*IS NULL*/

 SELECT * FROM dim_swjg t WHERE t.swjg_sj_id = '13300000000' ;

SELECT * FROM dim_swjg t START WITH t.swjg_dm = '13300000000' CONNECT BY t.swjg_sj_id = PRIOR t.swjg_dm ;

SELECT b.* FROM dim_swjg a JOIN dim_swjg b ON a.swjg_sj_id = b.swjg_id WHERE a.swjg_id = '13300000000';

SELECT * FROM dim_swjg t START WITH t.swjg_id = '13301100000' CONNECT BY PRIOR t.swjg_sj_id = t.swjg_id ;

SELECT a.*
        FROM dim_swjg a
       WHERE EXISTS (SELECT 1
                FROM dim_swjg b
               WHERE a.swjg_sj_id = b.swjg_sj_id
                 AND b.swjg_id = '13301100000');

WITH tmp AS
        (SELECT a.*, LEVEL lev
           FROM dim_swjg a
          START WITH a.swjg_sj_id = '00000000000'
         CONNECT BY a.swjg_sj_id = PRIOR a.swjg_id)
       SELECT *
         FROM tmp
        WHERE lev = (SELECT lev FROM tmp WHERE tmp.swjg_id = '13301100000');


WITH tmp AS
   (SELECT a.*, LEVEL lev
      FROM dim_swjg a
     START WITH a.swjg_sj_id = '00000000000'
    CONNECT BY a.swjg_sj_id = PRIOR a.swjg_id)
  SELECT b.*
    FROM tmp b,(
  SELECT *
    FROM tmp
   WHERE swjg_id = '13306810000' AND lev = '2') c
   WHERE b.lev = '2'
  UNION ALL
  SELECT *
    FROM tmp
   WHERE swjg_sj_id = (
  SELECT DISTINCT(d.swjg_id)
    FROM tmp d, tmp e,(
  SELECT *
    FROM tmp
   WHERE swjg_id = '13306810000' AND lev > '2') z
   WHERE e.swjg_id = z.swjg_sj_id AND d.swjg_id = e.swjg_sj_id);


 WITH tmp AS
   (SELECT a.*, LEVEL lev
      FROM dim_swjg a
     START WITH a.swjg_sj_id = '00000000000'
    CONNECT BY a.swjg_sj_id = PRIOR a.swjg_id)
  SELECT *
    FROM tmp
   WHERE lev = (SELECT lev FROM tmp WHERE swjg_id = '13306810000') - 1;


SELECT SYS_CONNECT_BY_PATH(mc, '/')
    FROM flfl
   WHERE ID = 6498
   START WITH sjflid IS NULL
  CONNECT BY sjflid = PRIOR ID;
  SELECT SYS_CONNECT_BY_PATH(swjg_mc, '-')
    FROM dim_swjg
   WHERE swjg_id = '13306810000'
   START WITH swjg_sj_id = '00000000000'
  CONNECT BY swjg_sj_id = PRIOR swjg_id;


SELECT SYS_CONNECT_BY_PATH(mc, '/')
    FROM flfl
   START WITH ID = 6498
  CONNECT BY PRIOR sjflid = ID;
  SELECT SYS_CONNECT_BY_PATH(swjg_mc, '-')
    FROM dim_swjg
   START WITH swjg_id = '13306810000'
  CONNECT BY PRIOR swjg_sj_id = swjg_id;


SELECT CONNECT_BY_ROOT swjg_mc, t.*
    FROM dim_swjg t
   START WITH t.swjg_id = '13306810000'
  CONNECT BY PRIOR t.swjg_sj_id = t.swjg_id;


SELECT CONNECT_BY_ISLEAF, t.*
    FROM dim_swjg t
   START WITH t.swjg_sj_id = '00000000000'
  CONNECT BY t.swjg_sj_id = PRIOR t.swjg_id;

執行個體：

1、資料庫表結構
organization 組織機構表
create table  ORGANIZATION( 
  OID       NUMBER(5) not null, ---組織機構ID 
  ONAME     VARCHAR2(40) not null ---組織機構名稱 
)
org_org 組織機構關系表
create table ORG_ORG( 
  UPID   NUMBER(5) not null,---上級部門id 
  DOWNID NUMBER(5) not null----下級部門id 
)
亮表通過OID和DOWNID關聯

2、所有組織機構樹
select c.oid as 部門ID,
       c.oname as 部門名稱,
       c.upid as 上級部門id,
       level as 樹深度,
       sys_connect_by_path(c.oname, '-') path as 樹路徑
  from (select decode(t.downid, null, o.oid, t.downid) as oid,
               o.oname,
               decode(t.upid, null, 0, t.upid) as upid
          from t_organization o, t_org_org t
         where o.oid = t.downid(+)) c
 start with c.upid = 0
connect by prior c.oid = c.upid

3、從某個部門開始向下查詢該部門樹c.oid=65為部門id
select c.oid as 部門ID,
       c.oname as 部門名稱,
       c.upid as 上級部門id,
       level as 樹深度,
       sys_connect_by_path(c.oname, '-') path as 樹路徑
  from (select decode(t.downid, null, o.oid, t.downid) as oid,
               o.oname,
               decode(t.upid, null, 0, t.upid) as upid
          from t_organization o, t_org_org t
         where o.oid = t.downid(+)) c
 start with c.oid = 65
connect by prior c.oid = c.upid

4、從下級部門開始向上查詢機構樹
select c.oid as 部門ID,
       c.oname as 部門名稱,
       c.upid as 上級部門id,
       level as 樹深度,
       sys_connect_by_path(c.oname, '-') path as 樹路徑
  from (select decode(t.downid, null, o.oid, t.downid) as oid,
               o.oname,
               decode(t.upid, null, 0, t.upid) as upid
          from t_organization o, t_org_org t
         where o.oid = t.downid(+)) c
 start with c.oid = 85
connect by c.oid = prior c.upid

5、給每一層級排序
加入 ORDER SIBLINGS BY  排序字句即可
例如：
SELECT LPAD('--', 2 * (LEVEL - 1)) || last_name &quot;
Reports To &quot;
, employee_id FROM hr.employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name
等。

\(^_^)/ Oracle 樹查詢

繼續閱讀

查詢表中重複記錄

ORACLE 表壓縮

MySQL安裝時常見問題和解決方案【詳解】

Doris資料更新及删除1.資料更新

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

單字母域名

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

testlink 切換資料庫

Oracle 批量查詢傳入List 傳回List