oracle递归sql查询语句,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