表结构:
ID Name ParentID
1 'aa'
2 'bb'
3 'cc' 1
4 'dd' 3
编写sql语句,删除ID=1及其所有子记录
结果:
ID Name ParentID
2 'bb'
ID Name ParentID
1 'aa'
2 'bb'
3 'cc' 1
4 'dd' 3
编写sql语句,删除ID=1及其所有子记录
结果:
ID Name ParentID
2 'bb'
调试欢乐多
给你一个递归查询的例子
例如:
--SQL中递归的使用
CREATE TABLE #BD_MTLCategory
(
CategoryId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CategoryLevel INT,
CategoryName VARCHAR(20),
ParentCategoryId INT
)INSERT INTO #BD_MTLCategory(CategoryLevel, CategoryName, ParentCategoryId)
SELECT 1, '纺织品类', -1 UNION ALL
SELECT 2, '网类', 1 UNION ALL
SELECT 2, '布类', 1 UNION ALL
SELECT 2, '绒类', 1 UNION ALL
SELECT 3, '斜纹布', 4 DECLARE @Level int, @CategoryId int, @ParentCategoryId int, @CategoryName VARCHAR(20)
DECLARE @StatckTB TABLE( CategoryId int, CategoryLevel int, CategoryName VARCHAR(20)) --临时数据集
DECLARE @TempTB TABLE( CategoryId int, Kind int, CategoryName VARCHAR(20) ) --最终数据SELECT @ParentCategoryId = 1, @Level = 1, @CategoryName = '纺织品类'
INSERT INTO @StatckTB VALUES( @ParentCategoryId, @Level, @CategoryName)
WHILE @Level > 0
BEGIN
IF EXISTS (SELECT * FROM @StatckTB WHERE CategoryLevel=@Level )
BEGIN
SELECT @CategoryId=CategoryId, @CategoryName = CategoryName FROM @StatckTB WHERE CategoryLevel=@Level
INSERT INTO @TempTB VALUES (@CategoryId, @ParentCategoryId, @CategoryName)
DELETE FROM @StatckTB WHERE CategoryId=@CategoryId AND CategoryLevel=@Level
INSERT INTO @StatckTB SELECT CategoryId, @Level+1, CategoryName FROM #BD_MTLCategory WHERE ParentCategoryId=@CategoryId AND CategoryId <> @CategoryId
IF @@ROWCOUNT > 0
SELECT @Level = @Level + 1
END
ELSE
SELECT @Level = @Level - 1
ENDSELECT * FROM @TempTB ORDER BY CategoryIdDROP TABLE #BD_MTLCategory
--实现split功能 的函数
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
endgo--测试数据
CREATE TABLE t_A
(ID int,name VARCHAR(200),ParentID int)
INSERT INTO t_A
SELECT 1,'aa',0
UNION ALL
SELECT 2,'bb',0
UNION ALL
SELECT 3,'cc',1
UNION ALL
SELECT 4,'dd',3go--求个节点下所有子节点:
create function f_cid(@id int)
returns varchar(500)
as
begin
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 @cids
end
goselect *,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--参数1
delete from t_A where id in (
select a.id from t_A a ,dbo.f_split(@var,'-') b
where a.id =b.a )select * from t_A--drop table t_A
--DROP FUNCTION dbo.f_cid
--DROP FUNCTION dbo.f_split
1、自定义了2个函数 一个是实现split功能的函数,另一个是个节点下所有子节点的函数。
2、用法:创建测试数据;新增列显示所有节点下的子节点;由于新增列数据是(‘1-3-4’),
所以用了个实现split功能的函数,并且把数据放到表中。
3、实现删除方法