请求SQL语句
表：
id name pid
01 张三 00
02 李四 00
03 王五 01
04 牛六 02
05 马七 04
06 张八 03

如何写delete语句，删除id=‘01’，同时把‘01’的所有子孙节点完全删除
例如：我删除id=‘01’后，表记录为
表：
id name pid
02 李四 00
04 牛六 02
05 马七 04

------解决方案--------------------
--建立函數
Create Function F_GetChildren(@id Varchar(10))
Returns @Tree Table (id Varchar(10), pid Varchar(10))
As
Begin
Insert @Tree Select id, pid From 表 Where id = @id
While @@Rowcount > 0
Insert @Tree Select A.id, A.pid From 表 A Inner Join @Tree B On A.pid = B.id And A.id Not In (Select id From @Tree)
Return
End
GO
--測試
Delete From 表 Where ID In (Select id From dbo.F_GetChildren( '01 '))

Select * From 表
------解决方案--------------------
--建立測試環境
Create Table 表
(id Varchar(10),
name Nvarchar(10),
pid Varchar(10)
)
Insert 表 Select '01 ', N '张三 ', '00 '
Union All Select '02 ', N '李四 ', '00 '
Union All Select '03 ', N '王五 ', '01 '
Union All Select '04 ', N '牛六 ', '02 '
Union All Select '05 ', N '马七 ', '04 '
Union All Select '06 ', N '张八 ', '03 '
GO
--建立函數
Create Function F_GetChildren(@id Varchar(10))
Returns @Tree Table (id Varchar(10), pid Varchar(10))
As
Begin
Insert @Tree Select id, pid From 表 Where id = @id
While @@Rowcount > 0
Insert @Tree Select A.id, A.pid From 表 A Inner Join @Tree B On A.pid = B.id And A.id Not In (Select id From @Tree)
Return
End
GO
--測試
Delete From 表 Where ID In (Select id From dbo.F_GetChildren( '01 '))

Select * From 表
GO
--刪除測試環境
Drop Table 表
Drop Function F_GetChildren
--結果
/*
id name pid
02 李四 00
04 牛六 02
05 马七 04
*/
------解决方案--------------------
--经过测试，触发器可以实现，代码如下
if exists(select name from sysobjects where name= 'test 'and type= 'U ')
drop table [dbo].[test]
go
create table [dbo].[test](id char(10),name char(20),pid char(10))
go
insert into test
select '00 ', 'aaa ', '00 '
union all select '01 ', 'aaa ', '00 '
union all select '02 ', 'aaa ', '00 '
union all select '03 ', 'aaa ', '01 '
union all select '04 ', 'aaa ', '01 '
union all select '05 ', 'aaa ', '02 '
union all select '06 ', 'aaa ', '02 '
union all select '07 ', 'aaa ', '04 '
union all select '08 ', 'aaa ', '04 '
union all select '09 ', 'aaa ', '05 '
union all select '10 ', 'aaa ', '05 '
go
if exists(select name from sysobjects where name= 'tg_test 'and type= 'TR ')
drop trigger [dbo].[tg_test]
go
create trigger [dbo].[tg_test]
on [dbo].[test]
after delete
as
begin
if exists(select 1 from test where pid in(select id from deleted ))
delete from test where pid in(select id from deleted )
end
go

select*from [dbo].[test]

delete from [dbo].[test] where id= '02 '

select*from [dbo].[test]
/*结果
id name pid
---------- -------------------- ----------
00