删除根的同时把叶子也删除掉

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int)
RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.parid=b.ID AND b.Level=@Level-1
END
RETURN
END
GO

create table tb(ID int , NAME varchar(10) , PARID int)
insert into tb values(1, 'A' , 0 )
insert into tb values(2, 'B' , 0 )
insert into tb values(3, 'C' , 0 )
insert into tb values(4, 'A_1_1' , 1 )
insert into tb values(5, 'A_1_2' , 1 )
insert into tb values(6, 'A_2_1' , 4 )
insert into tb values(7, 'A_2_2' , 4 )
go

declare @id as int
set @id = 1

--查询
SELECT a.* FROM tb a,f_Cid(@id) b WHERE a.ID=b.ID
/*
ID NAME PARID
----------- ---------- -----------
1 A 0
4 A_1_1 1
5 A_1_2 1
6 A_2_1 4
7 A_2_2 4
（所影响的行数为 5 行）
*/

--删除
delete from tb where id in (SELECT a.id FROM tb a,f_Cid(@id) b WHERE a.ID = b.ID)
--查询删除后的结果
select * from tb
/*
ID NAME PARID
----------- ---------- -----------
2 B 0
3 C 0
（所影响的行数为 2 行）
*/

drop table tb
drop function dbo.f_cid