CONAN ZONE

轉自：http://www.javaeye.com/topic/625574

有的情況下，我們需要用遞歸的方法整理數據，這才程序中很容易做到，但是在數據庫中，用SQL語句怎么實現？下面我以最典型的樹形結構來說明下如何在Oracle使用遞歸查詢。
    為了說明方便，創建一張數據庫表，用于存儲一個簡單的樹形結構

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。。。

    具體格式是：

SELECT column
FROM table_name
START WITH column=value
CONNECT BY PRIOR 父主鍵=子外鍵

    對于本例來說，就是：

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字段在每一個分支內進行排序，這個問題怎么處理呢？我們可能很自然的想到如下語句：

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

這顯然不是我們想要的結果，那下面的這個語句呢？

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”就可以實現我們的需求，用法如下：

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

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

至于在Oracle8以前版本，或者其他數據庫中如何實現類似的功能，希望大家來指教下，謝謝。