SQL 如果存在就更新，如果不存在就添加，使用 Merge 函数(SQ...

njl

SQL 如果存在就更新，如果不存在就添加，使用 Merge 函数(SQL2008版本及以上)
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