需要返积分的会员临时表
declare @member table ( id varchar(50), --用户ID TimesMoney decimal(18,2),--每日付款额度 ReadyMoney decimal(18,2)--已返利 ) insert into @member select M.Id ,U.BackBV , SUM(L.NomIn) as ReadyMoney from MemberAccount as M , UserStype as U ,ListPvorSv as L where M.DataState=1 and M.UserStype =U.Id and M.Id = L.MemberAccountId group by M.Id,U.BackBV select * from @member
--更新会员账户 update MemberAccount set PV=PV+h.TimesMoney from @member as h where MemberAccount.Id=h.id insert into ListPvorSv select ltrim(NEWID()),Mb.id,Mb.TimesMoney,0,'886FE96A-05F9-4F01-98B8-163FFD26B7BE',GETDATE(),'分红返利','SYS',1,'pv','',GETDATE(),'' from @member as Mb
整理了一下放入存储过程,按时间进行执行
--创建存储过程 if (exists (select * from sys.objects where name = 'proc_memberRebate_streetok')) drop proc proc_memberRebate_streetok go create proc proc_memberRebate_streetok as BEGIN declare @member table ( id varchar(50), --用户ID TimesMoney decimal(18,2),--每日付款额度 ReadyMoney decimal(18,2)--已返利 ) insert into @member select M.Id ,U.BackBV , SUM(L.NomIn) as ReadyMoney from MemberAccount as M , UserStype as U ,ListPvorSv as L where M.DataState=1 and M.UserStype =U.Id and M.Id = L.MemberAccountId group by M.Id,U.BackBV --更新会员账户 update MemberAccount set PV=PV+h.TimesMoney from @member as h where MemberAccount.Id=h.id insert into ListPvorSv select ltrim(NEWID()),Mb.id,Mb.TimesMoney,0,'886FE96A-05F9-4F01-98B8-163FFD26B7BE',GETDATE(),'分红返利','SYS',1,'pv','',GETDATE(),'' from @member as Mb end --执行存储过程 exec proc_memberRebate_streetok
到此就已经完成每日会员表返积分,并更新向数据库插入记录
小计:
--取当前时间是星期几 select DATENAME(dw,GETDATE()) --个别处理 declare @dayName nvarchar(10) set @dayName= DATENAME(dw,GETDATE()) if(@dayName!='星期五') begin exec proc_memberRebate_streetok end