Oracle递归查询树结构
之前在工作中碰到一个问题,需要找树结构下的所有子节点,如果用程序写会反复查询数据库,对性能有影响,在网上找了下,可以用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 = 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的