示例问题如下：
表结构:
id parentid
1 0
2 1
3 2
……
针对该表结构解释如下:
1的父节点为0,
2的父节点为1,
3的父节点为2
……
以此类推,要求给定一个父节点的值,比如1,
用sql语句查询的到该父结点下的所有子节点
下面的sql是在sql server下调试通过的,如果是oracle,则有connect by可以实现.
建立测试表:
drop table dbtree
create table dbtree
(
[id] int,
[name] nvarchar(20),
[parentid] int
)
插入测试数据:
insert into dbtree ([id],[parentid]) values (1,0)
insert into dbtree ([id],[parentid]) values (2,1)
insert into dbtree ([id],[parentid]) values (3,1)
insert into dbtree ([id],[parentid]) values (4,3)
insert into dbtree ([id],[parentid]) values (5,4)
insert into dbtree ([id],[parentid]) values (6,7)
insert into dbtree ([id],[parentid]) values (8,5)
实现方法一:
代码如下:
declare @id int
set @id = 1 —在次修改父节点
select * into #temp from dbtree where parentid in (@id)
select * into #allrow from dbtree where parentid in (@id) –1,2
while exists(select * from #temp)
begin
select * into #temp2 from #temp
truncate table #temp
insert into #temp select * from dbtree where parentid in (select id from #temp2)
insert into #allrow select * from #temp
drop table #temp2
end
select * from #allrow order by id
drop table #temp
drop table #allrow
实现方法二:
代码如下:
create table #allrow
(
id int,
parentid int
)
declare @id int
set @id = 1 —在次修改父节点
delete #allrow
–顶层自身
insert into #allrow (id,parentid) select @id, @id
while @@rowcount > 0
begin
insert into #allrow (id,parentid)
select b.id,a.id
from #allrow a,dbtree b
where a.id = b.parentid and
not exists (select id from #allrow where id = b.id and parentid = a.id)
end
delete from #allrow where id = @id
select * from #allrow order by id
drop table #allrow
实现方法三:
代码如下:
在sql server2005中其实提供了cte[公共表表达式]来实现递归:
关于cte的使用请查msdn
declare @id int
set @id = 3; —在次修改父节点
with rootnodecte(id,parentid)
as
(
select id,parentid from dbtree where parentid in (@id)
union all
select dbtree.id,dbtree.parentid from rootnodecte
inner join dbtree
on rootnodecte.id = dbtree.parentid
)
select * from rootnodecte