sql server 使用循环和临时表处理数据 而不用游标

declare @thismonth datetime;--本月第一秒
SELECT @thismonth=Convert(datetime, CONVERT(nvarchar(10),dateadd(dd,-day(getdate())+1,getdate()),121)+ ' 0:0' )
declare @lastmonth datetime;--上月第一秒
SELECT @lastmonth=Convert(datetime,CONVERT(nvarchar(10),dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())),121)+ ' 0:0' )
    
create table #temp_agentuser(id  int IDENTITY (1,1)  not null,agentid int );
insert into #temp_agentuser(agentid)
select id from tusers where level=1 and myupperid=0
select * from #temp_agentuser
--开始遍历所有代理
declare @maxId int;
declare @minId int;
select @maxId=isnull( max(ID),0) from #temp_agentuser;
select @minId=isnull( min(ID),0) from #temp_agentuser;
print @maxId
print @minId
if(@maxId<>0 and @minId<>0)--有代理的情况下
begin
declare @i int  
declare @agentid int
set @i=@minId
while @i<=@maxId --处理一个代理
	begin
	  select @agentid=agentid from #temp_agentuser where id=@i;
    declare @ThisMonthBalance decimal(18, 2);set @ThisMonthBalance=0;
    declare @LastMonthBalance decimal(18, 2);set @LastMonthBalance=0;
    declare @ChargeOfThisMonth decimal(18, 2);set @ChargeOfThisMonth=0;
    declare @DistillOfThisMonth decimal(18, 2);set @DistillOfThisMonth=0;
    declare @Achievement decimal(18, 2);set @Achievement=0;
    
    create table #temp_followuser(id  int IDENTITY (1,1)  not null,followuserid int,myupperid int );
    with w_Users(uid,myupperid) as  
    (  
          select id,myupperid from tusers where id=@agentid 
          union   all 
          select U.id,U.myupperid  from TUsers U,w_Users w where U.myupperId=W.uid 
    )
    insert into #temp_followuser( followuserid,myupperid) --包括他自己
    select uid,myupperid from w_Users;
    
    --本月余额
    select @ThisMonthBalance=isnull(sum(score),0) FROM TUsers 
    where 
    id in (select followuserid from #temp_followuser) and
    myupperId<>0;--除掉他自己的余额，那是个虚数，无意义
    
    
    
    --上月余额
    if(exists(select * FROM TAgentStat where AgentUserId=@agentid and [DateTime]=@lastmonth))
    begin
      select  @LastMonthBalance=ThisMonthBalance FROM TAgentStat where AgentUserId = @agentid and DateTime= @lastmonth
    end
    
    --本月充值
    select  @ChargeOfThisMonth=isnull(sum(Price),0) from TPaydetails join #temp_followuser on 
                  dbo.TPayDetails.UserId=#temp_followuser.followuserid and 
                  dbo.TPayDetails.ChargeUserUpperId=#temp_followuser.myupperid
    where 
                  dbo.TPayDetails.TransactionId is not null and dbo.TPayDetails.FinishedTime between @thismonth and GETDATE()
    
    --本月消费
     select  @DistillOfThisMonth=isnull(sum(TotalMoney),0) from TProductBuyHis join #temp_followuser on 
                  dbo.TProductBuyHis.UserId=#temp_followuser.followuserid and 
                  dbo.TProductBuyHis.UpperUserId=#temp_followuser.myupperid
    where 
                  dbo.TProductBuyHis.Status = 1 and 
                  dbo.TProductBuyHis.CreateTime between @thismonth and GETDATE() and
                  #temp_followuser.myupperid<>0 
                  
    
    set @Achievement= @ChargeOfThisMonth - @ThisMonthBalance + @LastMonthBalance - @DistillOfThisMonth
    
    DELETE FROM HalfBuy.dbo.TAgentStat 
    WHERE  AgentUserId = @agentid AND [DateTime] = @thismonth 
    
    INSERT INTO HalfBuy.dbo.TAgentStat
    (AgentUserId, [DateTime], ThisMonthBalance, LastMonthBalance, ChargeOfThisMonth, DistillOfThisMonth, Achievement) 
    VALUES (@agentid, @thismonth, @ThisMonthBalance, @LastMonthBalance, @ChargeOfThisMonth, @DistillOfThisMonth, @Achievement)

    drop table #temp_followuser
	  set @i=@i +1 ;
	end
end
drop table #temp_agentuser;