Oracle递归查询树结构

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 = 0 Connect by prior cid = cpid --找 子所在的根节点 select * from test1 start with cid = 3 Connect by prior cpid = cid select * from( select * from test1 start with cid = 3 Connect 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递归查询树结构

标签：结束   with   values   树结构   数据库   inner   tar   from   工作