oracle 树形结构单表 查询 递归SQL

Start with...Connect By子句递归查询

下面是从网上转载的内容：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

CREATE TABLE TBL_TEST

(

ID    NUMBER,

NAME  VARCHAR2(100 BYTE),

PID   NUMBER                                 DEFAULT0

);

 

#插入测试数据：

INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(''''1'''',''''10'''',''''0'''');

INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(''''2'''',''''11'''',''''1'''');

INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(''''3'''',''''20'''',''''0'''');

INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(''''4'''',''''12'''',''''1'''');

INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(''''5'''',''''121'''',''''2'''');

 

#从Root往树末梢递归

select * from TBL_TEST

start withid=1

connect by prior id = pid

 

#从末梢往树ROOT递归

select * from TBL_TEST

start withid=5

connect by prior pid = id