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;
sql server 使用循环和临时表处理数据 而不用游标
最新推荐文章于 2024-08-13 09:09:11 发布