oracle樹形查詢

sql樹形遞迴查詢是資料庫查詢的一種特殊情形，也是組織結構、行政區劃查詢的一種最常用的的情形之一。下面對該種查詢進行一些總結：

1、查詢某個節點下面子孫節點

select * from table start with id=1 connect by prior id=pid

2、查詢節點的祖先節點

select * from table start with id=5 connect by prior pid=id

在樹形結構節點很多的情況一下，一般會採用非同步重新整理的方式進行，在預設載入的情況下，會展開到某個層級。這種情況下，不但要獲取某個節點的祖先節點，還需要獲取祖先節點的兄弟節點，在這種情況下可以通過level進行

select id,pid,level from tb where level>1
connect by prior id=pid
start with id=1
order by level

顯示出樹的級別查詢

select menu_id,rpad(' ',(level-1)*4)||menu_name,level from menu   
 connect by parent_id = prior menu_id   
 start with parent_id is null;  

---

淺談oracle樹狀結構層級查詢

oracle樹狀結構查詢即層次遞迴查詢，是sql語句經常用到的，在實際開發中組織結構實現及其層次化實現功能也是經常遇到的，雖然我是一個java程式開發者，我一直覺得只要精通資料庫那麼對於java開發你就成功了三分之一，本篇中主要介紹start with...connect by prior 、order by 、sys_connect_by_path。

概要：樹狀結構通常由根節點、父節點、子節點和葉節點組成，簡單來說，一張表中存在兩個欄位，dept_id,par_dept_id,那麼通過找到每一條記錄的父級id即可形成一個樹狀結構，也就是par_dept_id（子）=dept_id（父），通俗的說就是這條記錄的par_dept_id是另外一條記錄也就是父級的dept_id，其樹狀結構層級查詢的基本語法是：

　　SELECT [LEVEL],*

　　FEOM table_name 

　　START WITH 條件1

　　CONNECT BY PRIOR 條件2

　　ORDER　BY　排序欄位

說明：

LEVEL—偽列，用於表示樹的層次
條件1—根節點的限定條件，當然也可以放寬許可權，以獲得多個根節點，也就是獲取多個樹
條件2—連線條件，目的就是給出父子之間的關係是什麼，根據這個關係進行遞迴查詢
排序欄位—對所有返回記錄進行排序

對prior說明：要的時候有兩種寫法：connect by prior dept_id=par_dept_id 或 connect by dept_id=prior par_dept_id，前一種寫法表示採用自上而下的搜尋方式（先找父節點然後找子節點），後一種寫法表示採用自下而上的搜尋方式（先找葉子節點然後找父節點）。

樹狀結構層次化查詢需要對樹結構的每一個節點進行訪問並且不能重複，其訪問步驟為：

大致意思就是掃描整個樹結構的過程即遍歷樹的過程，其用語言描述就是：

步驟一：從根節點開始；

步驟二：訪問該節點；

步驟三：判斷該節點有無未被訪問的子節點，若有，則轉向它最左側的未被訪問的子節，並執行第二步，否則執行第四步；

步驟四：若該節點為根節點，則訪問完畢，否則執行第五步；

步驟五：返回到該節點的父節點，並執行第三步驟。

除此之外，sys_connect_by_path函式是和connect by 一起使用的，在實戰中具體帶目的具體介紹！

實戰：最近做專案的組織結構，對於部門的各級層次顯示，由於這部分掌握不牢固，用最笨的like模糊查詢解決了，雖然功能實現了，但是問題很多，如擴充套件性不好，稍微改下需求就要進行大改，不滿意最後對其進行了優化。在開發中能用資料庫解決的就不要用java去解決，這也是我一直保持的想法並堅持著。

對於建表語句及其測試資料我放在另外一篇部落格中，需要進行測試的可以過去拷貝執行測試驗證下！

在這張表中有三個欄位：
dept_id 部門主鍵id;
dept_name 部門名稱;
dept_code 部門編碼；
par_dept_id 父級部門id(首級部門為 -1);

1.當前節點遍歷子節點（遍歷當前部門下所有子部門包括本身）

    select t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level
    from SYS_DEPT t
    start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'
    connect by prior t.dept_id = t.par_dept_id
    order by level, t.dept_code

結果：
dept_id=40288ac45a3c1e8b015a3c28b4ae01d6 是客運部主鍵，對其下的所有子部門進行遍歷，同時用 order by level,dept_code 進行排序 以便達到實際生活中想要的資料；共31條資料，部分資料如圖所示：

但是：
有問題啊，如果你想在上面的資料中獲取層級在2也就是level=2的所有部門，發現剛開始的時候介紹的語言不起作用？並且會報ORA-00933：sql命令未正確結束，why?

這個我暫時也沒有得到研究出理論知識，但是改變下where level='2'的位置發現才會可以的。錯誤的和正確的sql我們對比一下，以後會用就行，要是路過的大神知道為什麼，還請告知下，萬分感謝！

錯誤sql:

    select t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level
    from SYS_DEPT t 
    start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'
    connect by prior t.dept_id = t.par_dept_id
    where level = '2'
    order by level, t.dept_code

正確sql:

    select t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level 
    from SYS_DEPT t
    where level = '2'
    start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'
    connect by prior t.dept_id = t.par_dept_id
    order by level, t.dept_code

當然了，這個對其他形式的where過濾所有返回記錄沒有影響的，這個只是一個例外！

2.sys_connect_by_path函式求父節點到子節點路徑
簡單介紹下，在oracle中sys_connect_by_path與connect by 一起使用，也就是先要有或建立一棵樹，否則無用還會報錯。它的主要作用體現在path上即路徑，是可以吧一個父節點下的所有節點通過某個字元區分，然後連結在一個列中顯示。

sys_connect_by_path(column,clear),其中column是字元型或能自動轉換成字元型的列名，它的主要目的就是將父節點到當前節點的“path”按照指定的模式出現，char可以是單字元也可以是多字元，但不能使用列值中包含的字元，而且這個引數必須是常量，且不允許使用繫結變數，clear不要用逗號。

文字容易讓人疲勞，放圖和程式碼吧！

    select sys_connect_by_path(t.dept_name,'-->'),t.dept_id, t.dept_name, t.dept_code, t.par_dept_id, level 
    from SYS_DEPT t  
    start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6' 
    connect by prior t.dept_id = t.par_dept_id
    order by level, t.dept_code

結果：