sql oracle 递归查询语句 Oracle使用SQL语句进行递归查询

id

pid

问题描述：

1

0

2

0

3

1

4

1

5

2

6

2

7

3

8

4

9

5

取出id为1及其下属的所有的数据,结果为下

id

pid

1

0

3

1

4

1

7

3

8

4

SQL> CREATE TABLE TBL_TEST

2 (

3 ID NUMBER,

4 NAME VARCHAR2(100 BYTE),

5 PID NUMBER DEFAULT 0

6 );

Table created

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

1 row inserted

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

1 row inserted

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

1 row inserted

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

1 row inserted

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

1 row inserted

SQL>

SQL> select * from TBL_TEST

2 start with id=1

3 connect by prior id = pid

4 /

ID NAME PID

---------- -------------------------------------------------------------------------------- ----------

1 10 0

2 11 1

5 121 2

4 12 1

SQL>

SQL> select * from TBL_TEST

2 start with id=5

3 connect by prior pid = id

4 /

ID NAME PID

---------- -------------------------------------------------------------------------------- ----------

5 121 2

2 11 1

1 10 0