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

来源：互联网发布：slam 十四讲图优化编辑：程序博客网时间：2024/05/24 16:17

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