删除本节点及其子节点的所有数据

来源：互联网发布：mac安装win单系统编辑：程序博客网时间：2024/04/29 09:54

==>

删除ID是1及其所有子节点的数据

--实现split功能 的函数create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))returns @temp table(a varchar(100))as begindeclare @i intset @SourceSql=rtrim(ltrim(@SourceSql))set @i=charindex(@StrSeprate,@SourceSql)while @i>=1begininsert @temp values(left(@SourceSql,@i-1))set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)set @i=charindex(@StrSeprate,@SourceSql)endif @SourceSql<>'\'insert @temp values(@SourceSql)return endgo--测试数据CREATE TABLE t_A(ID int,name VARCHAR(200),ParentID int)INSERT INTO t_ASELECT 1,'aa',0UNION ALLSELECT 2,'bb',0UNION ALLSELECT 3,'cc',1UNION ALLSELECT 4,'dd',3go--求个节点下所有子节点：create function f_cid(@id int)returns varchar(500)asbegin     declare @t table(id int,desn varchar(10),parentid int ,lev int)     declare @lev int     set @lev=1     insert into @t select *,@lev from  t_A where id=@id     while(@@rowcount>0)     begin          set @lev=@lev+1          insert into @t select a.*,@lev from t_A a,@t b          where a.parentid=b.id and b.lev=@lev-1     end     declare @cids varchar(500)     select @cids=isnull(@cids+'-','')+ltrim(id) from @t order by lev     return @cidsendgoselect *,dbo.f_cid(id) as jd from t_A--删除数据declare @var varchar(500)select @var=dbo.f_cid(id)  from t_A where id = 1--参数1delete from t_A where id in (select a.id  from t_A a ,dbo.f_split(@var,'-') bwhere a.id =b.a )select * from t_A--drop table t_A--DROP FUNCTION dbo.f_cid--DROP FUNCTION dbo.f_split