Oracle遞迴查詢__Oracle

      有的情況下，我們需要用遞迴的方法整理資料，這才程式中很容易做到，但是在資料庫中，用SQL語句怎麼實現。下面我以最典型的樹形結構來說明下如何在Oracle使用遞迴查詢。
    為了說明方便，建立一張資料庫表，用於儲存一個簡單的樹形結構 create table TEST_TREE    (      ID   NUMBER,      PID  NUMBER,      IND  NUMBER,      NAME VARCHAR2(32)    )  

create table TEST_TREE(  ID   NUMBER,  PID  NUMBER,  IND  NUMBER,  NAME VARCHAR2(32))

    ID是主鍵，PID是父節點ID，IND是排序欄位，NAME是節點名稱。初始化幾條測試資料。

 

ID	PID	IND	NAME
1	0	1	根節點
2	1	1	一級菜單1
3	1	2	一級菜單2
4	1	2	一級菜單3
5	2	1	一級1子1
6	2	2	一級1子2
7	4	1	一級3子1
8	4	2	一級3子2
9	4	3	一級3子3
10	4	0	一級3子0

 

一、基本使用：

    在Oracle中，遞迴查詢要用到start   with 。。。。connect   by   prior。。。

    具體格式是： Sql代碼   SELECT column   FROM table_name    START WITH column=value    CONNECT BY PRIOR 父主鍵=子外鍵  

SELECT columnFROM table_nameSTART WITH column=valueCONNECT BY PRIOR 父主鍵=子外鍵

    對於本例來說，就是： Sql代碼   select   d.*   from  test_tree d       start   with   d.pid=0      connect   by   prior   d.id=d.pid   

select   d.*   from  test_tree d   start   with   d.pid=0  connect   by   prior   d.id=d.pid 

   查詢結果如下：

 

ID	PID	IND	NAME
1	0	1	根節點
2	1	1	一級菜單1
5	2	1	一級1子1
6	2	2	一級1子2
3	1	2	一級菜單2
4	1	2	一級菜單3
7	4	1	一級3子1
8	4	2	一級3子2
9	4	3	一級3子3
10	4	0	一級3子0

 我們從結果中可以看到，記錄已經是按照樹形結構進行排列了，但是現在有個新問題，如果我們有這樣的需求，就是不但要求結果按照樹形結構顯示，還要根據ind欄位在每一個分支內進行排序，這個問題怎麼處理呢。我們可能很自然的想到如下語句： Sql代碼   select   d.*   from  test_tree d       start   with   d.pid=0      connect   by   prior   d.id=d.pid       order by d.ind  

select   d.*   from  test_tree d   start   with   d.pid=0  connect   by   prior   d.id=d.pid   order by d.ind

 結果如下：

 

ID	PID	IND	NAME
1	0	1	根節點
2	1	1	一級菜單1
5	2	1	一級1子1
6	2	2	一級1子2
4	1	2	一級菜單3
10	4	0	一級3子0
8	4	2	一級3子2
9	4	3	一級3子3
7	4	1	一級3子1
3	1	2	一級菜單2

這顯然不是我們想要的結果，那下面的這個語句呢。 Sql代碼   select   d.*   from  (select dd.* from test_tree dd order by dd.ind) d       start   with   d.pid=0      connect   by   prior   d.id=d.pid   

select   d.*   from  (select dd.* from test_tree dd order by dd.ind) d   start   with   d.pid=0  connect   by   prior   d.id=d.pid 

 

結果如下：

 

ID	PID	IND	NAME
1	0	1	根節點
2	1	1	一級菜單1
5	2	1	一級1子1
6	2	2	一級1子2
4	1	2	一級菜單3
10	4	0	一級3子0
8	4	2	一級3子2
9	4	3	一級3子3
7	4	1	一級3子1
3	1	2	一級菜單2

這個結果看似對了，但由於一級菜單3節點下有一個節點的ind=0，導致一級菜單2被拍到了3下面。如果想使用類似這樣的語句做到各分支內排序，則需要找到一個能夠準確描述菜單層級的欄位，但是對於樣本表來說，不存在這麼一個欄位。

 

那我們如何?需求呢。其實Oracle9以後，提供了一種排序“order siblings by”就可以實現我們的需求，用法如下： Sql代碼   select   d.*   from  test_tree d       start   with   d.pid=0      connect   by   prior   d.id=d.pid       order   siblings   by   d.ind   asc  

select   d.*   from  test_tree d   start   with   d.pid=0  connect   by   prior   d.id=d.pid   order   siblings   by   d.ind   asc

 

結果如下：

 

ID	PID	IND	NAME
1	0	1	根節點
2	1	1	一級菜單1
5	2	1	一級1子1
6	2	2	一級1子2
3	1	2	一級菜單2
4	1	2	一級菜單3
10	4	0	一級3子0
7	4	1	一級3子1
8	4	2	一級3子2
9	4	3	一級3子3

 

這樣一來，查詢結果就完全符合我們的要求了。