ORACLE 树结构及查询

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表：
CREATE TABLE TBL_TEST
(
  ID    NUMBER,
  NAME  VARCHAR2(100 BYTE),
  PID   NUMBER                                  DEFAULT 0
);

插入测试数据：
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');

表结构：
        ID NAME                                                                                    PID
---------- -------------------------------------------------------------------------------- ----------
         1 10                                                                                        0
         2 11                                                                                        1
         3 20                                                                                        0
         4 12                                                                                        1
         5 121                                                                                       2


从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
        ID NAME                                                                                    PID
---------- -------------------------------------------------------------------------------- ----------
         1 10                                                                                        0
         2 11                                                                                        1
         5 121                                                                                       2
         4 12                                                                                        1

说明：从ID = 1开始，查到下一行时，这时上一行的ID =1=这一行的PID


从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id
        ID NAME                                                                                    PID
---------- -------------------------------------------------------------------------------- ----------
         5 121                                                                                       2
         2 11                                                                                        1
         1 10                                                                                        0

说明：从ID = 5开始，这时PID = 2,查到下一行时，上一行的PID=2=这行的ID。



总结：主要看prior在哪个字符上，与前面的start with 一样，则Root往树末梢递归，否则从末梢往树ROOT递归