22,210
社区成员
发帖
与我相关
我的任务
分享
--查所有子结点
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.Cid,@l from Catalogue as a,@re as b
where b.id=a.parentid and b.level=@l-1
end
return
end
go
select * from Catalogue a,dbo.f_getC(3) b where a.cid=b.id
/*
Cid CName Remark ParentId id level
----------- ---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------- -----------
3 部门文档 各部门的文件分类 0 3 0
4 研发部 研发部相关资料 3 4 1
5 部门员工介绍 介绍所有研发部员工的基本信息。 3 5 1
(所影响的行数为 3 行)
*/
delete a from Catalogue a,dbo.f_getC(3) b where a.cid=b.id
--再查询一次,就知道了