--递归删除父节点及所有子节点
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
