USE [NationalUnion] GO /****** Object: StoredProcedure [dbo].[proc_DataSummary] Script Date: 07/03/2014 15:33:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO alter proc [dbo].[proc_DataSummary20140703] ( @UserID bigint--, --@Level int ) as begin begin try begin tran declare @Level int set @Level=1 --默认Level为1为最后一级分享者 ----------------------------------------PV--------------------------------------------------------------------------------------------------------------------------------- --PV merge into dbo.DataSummary as DS using (select SharedUserID,PlatformID,CONVERT(varchar(100), CreateDate, 111) as CreatDate,ChannelID,SharedManagerID,COUNT(*) as Qty from dbo.PVInfo where SharedLevel = @Level and (@UserID<0 or SharedUserID = @UserID) group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateDate, 111)) as P on (DS.UserID=P.SharedUserID and DS.PlatformID=P.PlatformID and DS.ChannelID=P.ChannelID and DS.ManagerID=P.SharedManagerID and DS.SummaryDate=P.CreatDate) when matched then update set PV=(PV+P.Qty)--更新统计己存在的信息 when not matched then--统计新的PV信息 insert values(P.SharedUserID,P.PlatformID,P.CreatDate,P.ChannelID,P.SharedManagerID,P.Qty,0,0,0,0,0,0,GETDATE()); ----------------------------------------UV--------------------------------------------------------------------------------------------------------------------------------- --UV merge into dbo.DataSummary as DS using (select SharedUserID,PlatformID,CONVERT(varchar(100), CreateDate, 111) as CreatDate,ChannelID,SharedManagerID,COUNT(*) as Qty from dbo.UVInfo where SharedLevel = @Level and (@UserID<0 or SharedUserID = @UserID) group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateDate, 111)) as U on (DS.UserID=U.SharedUserID and DS.PlatformID=U.PlatformID and DS.ChannelID=U.ChannelID and DS.ManagerID=U.SharedManagerID and DS.SummaryDate=U.CreatDate) when matched then update set UV=(UV+U.Qty)--更新统计己存在的信息 when not matched then--统计新的PV信息 insert values(U.SharedUserID,U.PlatformID,U.CreatDate,U.ChannelID,U.SharedManagerID,0,U.Qty,0,0,0,0,0,GETDATE()); ----------------------------------------预计可算拥金--------------------------------------------------------------------------------------------------------------------------------- merge into dbo.DataSummary as DS using (select SharedUserID,PlatformID,CONVERT(varchar(100), CreateTime, 111) as CreatDate,ChannelID,SharedManagerID,sum(Commission) as TotalEstimateCommission from dbo.CPSOriDataOccur group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateTime, 111)) as O on (DS.UserID=O.SharedUserID and DS.PlatformID=O.PlatformID and DS.SummaryDate=O.CreatDate and DS.ChannelID=O.ChannelID and DS.ManagerID=O.SharedManagerID) when matched then--预计可算拥金 update set ds.CommissionEstimate=(ds.CommissionEstimate+O.TotalEstimateCommission) when not matched then insert values( O.SharedUserID,O.PlatformID,O.CreatDate,O.ChannelID,O.SharedManagerID,0,0,0,0,O.TotalEstimateCommission,0,0,GETDATE()); ----------------------------------------可结算拥金--------------------------------------------------------------------------------------------------------------------------------- merge into dbo.DataSummary as DS using (select SharedUserID,PlatformID,CONVERT(varchar(100), CreateTime, 111) as CreatDate,ChannelID,SharedManagerID,SUM(Price) as TotalPrice,sum(Commission) as TotalCommission from dbo.CPSOriDataEffect group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateTime, 111)) as E on (DS.UserID=E.SharedUserID and DS.PlatformID=E.PlatformID and DS.SummaryDate=E.CreatDate and DS.ChannelID=E.ChannelID and DS.ManagerID=E.SharedManagerID) when matched then update set ds.OrderAmount=(ds.OrderAmount+E.TotalPrice),ds.AvaliableCommission=(ds.CommissionEstimate+E.TotalCommission) when not matched then insert values( E.SharedUserID,E.PlatformID,E.CreatDate,E.ChannelID,E.SharedManagerID,0,0,0,E.TotalPrice,0,E.TotalCommission,0,GETDATE()); commit tran end try begin catch rollback tran end catch end
SQL 如果存在就更新,如果不存在就添加,使用 Merge 函数(SQL2008版本及以上),布布扣,bubuko.com