（转）sql语句递归查询和删除问题

SQL code if object_id('tb') is not null drop table tb go create table tb(id int,name varchar(50),parentId int) go insert into tb select 1 , '工具', 0 union all select 2 , '工具1', 1 union all select 3 , '工具2', 1 union all select 4 , '工具3', 1 union all select 6 , '工具21', 2 union all select 7 , '工具311', 3 union all select 8 , '工具211', 6 union all select 9 , '系统', 0 union all select 10, '系统1' , 9 go -- 2000 写法 if object_id('dbo.f_getchirdNode()') is not null drop function dbo.f_getchirdNode go create function f_getchirdNode(@id int) returns @t_level table(id varchar(3) , level int) begin declare @level int set @level = 1 insert into @t_level select @id , @level while @@ROWCOUNT > 0 begin set @level = @level + 1 insert into @t_level select a.id , @level from tb a , @t_Level b where a.parentId = b.id and b.level = @level - 1 end return end go select * from tb a where exists (select * from f_getchirdNode(1) b where a.id = b.id) -- 查询 delete from tb where exists (select * from f_getchirdNode(1) b where tb.id = b.id) -- 删除 go -- 2005 declare @id int set @id = 1 ;with liang as ( select * from tb where id = @id union all select a.* from tb a,liang b where a.parentId = b.id ) delete from tb where exists (select 1 from liang where tb.id = liang.id) -- 删除 --select * from liang --查询 select * from tb drop table tb