递归删除父节点及所有子节点（转）

--递归删除父节点及所有子节点
create table tb(Id int, ParentId int, Name varchar(5))
insert into tb select 1, 0, 'a1' 
union all select 2,2, 'a2' 
union all select 14, 1, 'b11' 
union all select 15, 1, 'b12' 
union all select 16, 14, 'c13' 
union all select 17, 14, 'c14'
union all select 104,17,'d15'
go
WITH temptab(id, parentid, name) AS
   (    SELECT root.id, root.parentid, root.name
FROM tb root
WHERE id=1
UNION ALL
SELECT sub.id, sub.parentid, sub.name
FROM tb sub, temptab super
WHERE sub.parentid = super.id
   )
delete from tb where id in(
select id from temptab
)
select * from tb
go 
drop table tb 
/*
Id          ParentId    Name
----------- ----------- -----
2           2           a2

转载于:https://www.cnblogs.com/jiajiayuan/archive/2011/09/02/2163779.html