有树型结构表TREE
Treeid, uptreeid, treename
1 0 a
2 0 b
3 1 c
4 1 d
5 3 e
6 3 f
7 5 g
8 4 h
9 2 i
如果我要删除Treeid=1时要删除它下面的所有子节点的数据(3,4,5,6,7,8),不用存储过程用SQL能实现么
Treeid, uptreeid, treename
1 0 a
2 0 b
3 1 c
4 1 d
5 3 e
6 3 f
7 5 g
8 4 h
9 2 i
如果我要删除Treeid=1时要删除它下面的所有子节点的数据(3,4,5,6,7,8),不用存储过程用SQL能实现么
insert into tselect 1, 0, 'a' union all
select 2, 0, 'b' union all
select 3, 1, 'c' union all
select 4, 1, 'd' union all
select 5, 3, 'e' union all
select 6, 3, 'f' union all
select 7, 5, 'g' union all
select 8, 4, 'h' union all
select 9, 2, 'i'create function dbo.aa(@i int)
returns @t table(treeid int,uptreeid int,level int)
as
begin
declare @level int
set @level=1
insert into @t
select a.Treeid,a.uptreeid,@level from t join t a on t.Treeid=a.uptreeid where t.Treeid=@i
while @@rowcount>0
begin
set @level=@level+1
insert into @t
select a.Treeid,a.uptreeid,@level from t a left join @t b on a.uptreeid=b.treeid
where b.level=@level-1
end
return
end1:
delete t
where Treeid in (select treeid from dbo.aa(1))2:select * from t
Treeid uptreeid treename
----------- ----------- ----------
1 0 a
2 0 b
9 2 i(3 row(s) affected)