时间戳

首页 > 解决方案 > 虽然循环在 SQL Server 中花费了很多时间

sql - 虽然循环在 SQL Server 中花费了很多时间

问题描述

在我的存储过程中，我有 2 个 while 循环，我需要避免这些循环并使用游标或递归 cte

create procedure Parent_Child_UserDetails_Schedule  
as  
begin  
    Set nocount on  

    create table #temptab(id int, userid int, parentid int)   

    select userid, 1 as valid 
    into #users 
    from userdetails 
    where isactive = 1  

    truncate table Parent_Child_UserDetails  

    while(select count(*) from #users where valid = 1) > 0  
    begin  
        declare @userid  int  

        select top 1 @userid = userid 
        from #users 
        where valid = 1   

        truncate table #temptab  

        insert into #temptab(id, userid, parentid)  
        values(1, @userid, @userid)  

        declare @id int            
        set @id = 1

        while((select count(*) from userdetails
               where parentid in (select userid from #temptab where id=@id ) and isactive = 1) > 0)            
        begin            
            insert into #temptab (id, userid, parentid) 
                select @id + 1, userid, @userid  
                from userdetails 
                where parentid in (select userid from #temptab where id = @id) 
                  and isactive = 1

            set @id= @id + 1
         end   

         insert into Parent_Child_UserDetails(Parentid, Userid)
             select parentid, userid 
             from #temptab   

         update #users 
         set valid = 0 
         where userid = @userid  
    end  

    drop table #temptab  
    drop table #users  

    Set nocount off  
end

请帮我 .....

标签： sqlsql-servertsqlsql-server-2008

解决方案

按照我们在评论中的对话，您需要将Parent_Child_UserDetails所有父 id 和用户 id 都插入到表中，其中两者都处于活动状态，您可以用单个语句替换代码的RBAR噩梦，如下所示：insert....select

insert into Parent_Child_UserDetails(Parentid, Userid)
select parentid, userid 
from userdetails as t0
where isactive = 1
and exists
(
    select 1
    from userdetails as t1
    where t1.isavtice = 1
    and t1.userId = t0.ParentId
)

推荐阅读