Some print.
Some print.
~SingleForum~无废话记录~ + ~19~记录点有用的好吗？少说废话少装b~

~SingleForum~无废话记录~ » other

Oracle递归查询

Started 2 years ago by admin. | Tags: .

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。

创建示例表：

PLAIN TEXT
SQL:
  1. CREATE TABLE TBL_TEST
  2.  
  3. (
  4.  
  5. ID    NUMBER,
  6.  
  7. NAME VARCHAR2(100 BYTE),
  8.  
  9. PID   NUMBER                                  DEFAULT 0
  10.  
  11. );

插入测试数据：

PLAIN TEXT
SQL:
  1. INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
  2.  
  3. INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
  4.  
  5. INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
  6.  
  7. INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
  8.  
  9. INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归

PLAIN TEXT
SQL:
  1. SELECT * FROM TBL_TEST
  2.  
  3. start WITH id=1
  4.  
  5. connect BY prior id = pid

从末梢往树ROOT递归

PLAIN TEXT
SQL:
  1. SELECT * FROM TBL_TEST
  2.  
  3. start WITH id=5
  4.  
  5. connect BY prior pid = id

RSS feed for this topic