ORACLE樹形結構查詢

在ORACLE資料庫查詢中,我們經常會遇到對樹型結構表的查詢,這是個麻煩的問題。下面給大家介紹一種SQL語句，實現遞迴查詢。文法如下：

SELECT 欄位1，欄位2，欄位3，。。。

FROM 表名

START WITH 條件1

CONNECT BY PRIOR 條件2

WHERE 條件3；

下面舉一個例子，有這樣一張表：

表dg_test

—————————————————————————————————————

parent_id                     child_id                         id_desc

—————————————————————————————————————

0000                             1001                            rows0

0000                             1002                            rows1

0000                             1003                            rows2

0000                             1004                            rows3

1001                             2001                            rows4

1001                             2002                            rows5

1002                             2003                            rows6

1003                             2004                            rows7

2001                             3001                            rows8

3001                             4001                            rows9

——————————————————————————————————————

使用如下SQL 陳述式：

SELECT * FROM dg_test START WITH parent_id = '1001' CONNECT BY PRIOR child_id = parent_id；

查詢結果如下：

—————————————————————————————————————

parent_id                     child_id                         id_desc

—————————————————————————————————————

1001                             2001                            rows4

1001                             2002                            rows5

2001                             3001                            rows8

3001                             4001                            rows9

——————————————————————————————————————