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递归