oracle 树形结构单表 查询 递归SQL

Start with...Connect By子句递归查询

下面是从网上转载的内容：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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 '' '' );   #从Root往树末梢递归 select * from TBL_TEST start with id=1 connect by prior id = pid   #从末梢往树ROOT递归 select * from TBL_TEST start with id=5 connect by prior pid = id