Oracle遞迴查詢樹結構

標籤：結束   with   values   樹結構   資料庫   inner   tar   from   工作   

之前在工作中碰到一個問題，需要找樹結構下的所有子節點，如果用程式寫會反覆查詢資料庫，對效能有影響，在網上找了下，可以用Oracle的遞迴查詢，例子如下：

create table test1 (cid int,cpid int)insert into test1 (cid,cpid) values(1,0);insert into test1 (cid,cpid) values(2,1);insert into test1 (cid,cpid) values(3,1);insert into test1 (cid,cpid) values(4,2);insert into test1 (cid,cpid) values(5,3);insert into test1 (cid,cpid) values(6,3);select * from test1--找根的所有子select * from test1 start with cpid = 0Connect by prior cid = cpid--找 子所在的根節點select * from test1 start with cid = 3Connect by prior cpid = cidselect * from(select * from test1 start with cid = 3Connect by prior cpid = cid) where cid <> 3

另外，SQLServer查詢樹結構下的所有子節點可以用如下語句:

 WITH cteTree        AS (SELECT *              FROM test1               WHERE CId = @TreeId  --第一個查詢作為遞迴的基點(錨點)            UNION ALL            SELECT test1.*     --第二個查詢作為遞迴成員， 下屬成員的結果為空白時，此遞迴結束。              FROM                   cteTree INNER JOIN test1  ON cteTree.CId = test1 .PId)         SELECT *          FROM cteTree 

太晚了，明天再完善SQLServer的

 

Oracle遞迴查詢樹結構