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