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');
從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
=====
對于oracle進行簡單樹查詢(遞歸查詢)
DEPTID PAREDEPTID NAME
NUMBER NUMBER CHAR (40 Byte)
部門id 父部門id(所屬部門id) 部門名稱
通過子節點向根節點追朔.
Sql代碼
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
Sql代碼
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
通過根節點遍歷子節點.
Sql代碼
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
Sql代碼
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
可通過level 關鍵字查詢所在層次.
Sql代碼
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
Sql代碼
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
再次復習一下:start with ...connect by 的用法， start with 后面所跟的就是就是遞歸的種子。
遞歸的種子也就是遞歸開始的地方 connect by 后面的"prior" 如果缺省：則只能查詢到符合條件的起始行，并不進行遞歸查詢；
connect by prior 后面所放的字段是有關系的，它指明了查詢的方向。
練習： 通過子節點獲得頂節點
Sql代碼
select FIRST_VALUE(deptid) OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid
====這種方法只是當表里就有一顆樹,多棵樹怎么辦？
聲明：JavaEye文章版權屬于作者，受法律保護。沒有作者書面許可不得轉載。
回復 更多評論