--生成测试数据
create table BOM(ID INT,Title VARCHAR(1000),Parent_ID INT)
insert into BOM select 1,'物品装备',0
insert into BOM select 2,'武器 ',1
insert into BOM select 3,'药品 ',1
insert into BOM select 4,'刀 ',2
insert into BOM select 5,'剑 ',2
insert into BOM select 6,'1111111 ',0
insert into BOM select 7,'sub111 ',6
go--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),Parent_ID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,Parent_ID,@i from BOM where ID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.Parent_ID,@i
from
BOM a,@t b
where
a.Parent_ID=b.ID and b.Level = @i-1
end
return
end
go--执行删除
delete from BOM where ID in (select ID from dbo.f_getChild(1))
--输出结果
select * from BOM
/*
ID Title Parent_ID
---- ------- -------
6 1111111 0
7 sub111 6
*/--删除测试数据
drop function f_getChild
drop table BOM
create table BOM(ID INT,Title VARCHAR(1000),Parent_ID INT)
insert into BOM select 1,'物品装备',0
insert into BOM select 2,'武器 ',1
insert into BOM select 3,'药品 ',1
insert into BOM select 4,'刀 ',2
insert into BOM select 5,'剑 ',2
insert into BOM select 6,'1111111 ',0
insert into BOM select 7,'sub111 ',6
go--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),Parent_ID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,Parent_ID,@i from BOM where ID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.Parent_ID,@i
from
BOM a,@t b
where
a.Parent_ID=b.ID and b.Level = @i-1
end
return
end
go--执行删除
delete from BOM where ID in (select ID from dbo.f_getChild(1))
--输出结果
select * from BOM
/*
ID Title Parent_ID
---- ------- -------
6 1111111 0
7 sub111 6
*/--删除测试数据
drop function f_getChild
drop table BOM
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货