如何删除一个节点及其下面的所有子节点?
---测试数据
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[table1] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [dbo].[table1]
create Table table1(
fdId varchar(50),
fdName varchar(50),
fdFatherId varchar(50),
fdLevel varchar(50)
)
insert into table1 select '1 ', '全部产品 ', '0 ', '0 '
insert into table1 select '2 ', '家电产品 ', '1 ', '1 '
insert into table1 select '3 ', '小家电 ', '2 ', '2 '
insert into table1 select '4 ', '空调系列 ', '2 ', '2 '
insert into table1 select '5 ', '格力空调 ', '4 ', '3 '
insert into table1 select '6 ', '冰箱系列 ', '2 ', '2 '
insert into table1 select '7 ', '床上用品 ', '1 ', '1 '
select * from table1
/*
fdId fdName fdFatherId fdLevel
1全部产品00
2家电产品11
3小家电22
4空调系列22
5格力空调43
6冰箱系列22
7床上用品11
fdId为该物品类的编号
fdName为名称
fdFatherId为所属父节点编号
(即属于在那个fdId之下)
fdLevel为在TreeView所属的等级
对应的TreeView显示是这样的
全部产品
--家电产品
--小家电
--空调系列
--格力空调
--冰箱系列
--床上用品
问题:怎么删除一个节点,其下级所有子节点都删除呢?
如删除家电产品:哪么就要删除:家电产品,小家电,空调系列,格力空调,冰箱系列
这个算法sql语句怎么写呀,我是用delphi的,写成delphi或sql存储过程都行
*/
[解决办法]
--生成测试数据
create table BOM(ID INT,Topic_ID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go
--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),Topic_ID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,Topic_ID,@i from BOM where Topic_ID = @ID
while @@rowcount <> 0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.Topic_ID,@i
from
BOM a,@t b
where
a.Topic_ID=b.ID and b.Level = @i-1
end
return
end
go
--创建用户定义存储过程
create procedure sp_relationDel(@var varchar(100))
as
begin
declare @ID varchar(100)
while charindex( ', ',@var)> 0
begin
set @ID = left(@var,charindex( ', ',@var)-1)
set @var = stuff(@var,1,charindex( ', ',@var), ' ')
delete BOM where ID in(select ID from dbo.f_getChild(@ID))
end
delete BOM where ID in(select ID from dbo.f_getChild(@var))
end
go
--执行删除操作
exec sp_relationDel '3,5,6 '
--删除测试数据
drop procedure sp_relationDel
drop function f_getChild
drop table BOM
[解决办法]
--建立函数
create function dbo.tree(@i varchar(50))
returns @t table(fdId varchar(50),fdfatherid varchar(50),level int)
as
begin
declare @level int
set @level=0
insert into @t
select fdId,fdfatherid,@level from table1 where table1.fdId=@i
while @@rowcount> 0
begin
set @level=@level+1
insert into @t
select a.fdId,a.fdfatherid,@level from table1 a left join @t b on a.fdfatherid=b.fdId
where b.level=@level-1
end
return
end
--删除 '2 '及其子节点
delete table1
where fdId in (select fdId from dbo.tree( '2 '))
--检索数据
select * from table1
-------------------
fdId fdName fdFatherId fdLevel
1 全部产品0 0
7 床上用品1 1
[解决办法]
create Table table1(fdId varchar(50),fdName varchar(50),fdFatherId varchar(50),fdLevel varchar(50))
insert into table1 select '1 ', '全部产品 ', '0 ', '0 '
insert into table1 select '2 ', '家电产品 ', '1 ', '1 '
insert into table1 select '3 ', '小家电 ', '2 ', '2 '
insert into table1 select '4 ', '空调系列 ', '2 ', '2 '
insert into table1 select '5 ', '格力空调 ', '4 ', '3 '
insert into table1 select '6 ', '冰箱系列 ', '2 ', '2 '
insert into table1 select '7 ', '床上用品 ', '1 ', '1 '
DECLARE @t_Level TABLE(ID varchar(50),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT fdId,@Level,fdId
FROM table1
WHERE fdFatherId =0
WHILE @@ROWCOUNT> 0
BEGIN
SET @Level=@Level+1
INSERT @t_Level
SELECT a.fdId,@Level,b.Sort+a.fdId
FROM table1 a,@t_Level b
WHERE a.fdFatherId=b.id
AND b.Level=@Level-1
END
--显示结果
SELECT SPACE(b.Level*2)+ '|-- '+a.fdName
FROM table1 a,@t_Level b
WHERE a.fdId=b.ID
ORDER BY b.Sort
drop table table1
/*
全部产品
--家电产品
--小家电
--空调系列
--格力空调
--冰箱系列
--床上用品
*/