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

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

2007-01-11 19:05

问题描述：
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