sql with递归

时间：2023-03-08 20:34:44
   with temp as
(
select Id, UserId, OfficeID, RoleId, DeptId, IsDelete, IsEnd, ParentId
from [dbo].[DiGui_Demo]
where UserId =1 or OfficeID = 56 or DeptId = 77
union all
select  a.Id,  a.UserId,  a.OfficeID,  a.RoleId,  a.DeptId,  a.IsDelete,  a.IsEnd, a. ParentId
from [DiGui_Demo] a
inner join temp on a.[parentid] = temp.[Id]
)
select distinct * from temp where IsEnd = 1 

=====================================================

create proc proc_DeleteDict
(
@autoId int
)
as
begin
with TEC as
(
select * from OA_Dict where AutoId = @autoId
union all
select a.* from OA_Dict a inner join TEC b on a.ParentId = b.AutoId
)  
update OA_Dict set IsDelete = 1 where AutoId in 
(
 select autoid from TEC
 )

end

相关文章