首页 > 代码库 > SQL 实现,如果存在就更新,如果不存在就添加
SQL 实现,如果存在就更新,如果不存在就添加
alter proc proc_DataSummaryasbegin begin try begin tran --PV --统计的信息存入临时表 select SharedUserID,PlatformID,CONVERT(varchar(100), CreateDate, 111) as CreatDate,ChannelID,SharedManagerID,COUNT(*) as Qty into #PVData from dbo.PVInfo where SharedLevel=1 group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateDate, 111) --更新将统计己存在的信息 update dbo.DataSummary set PV=dbo.#PVData.Qty from #PVData where dbo.DataSummary.UserID=dbo.#PVData.SharedUserID and dbo.DataSummary.PlatformID=dbo.#PVData.PlatformID and dbo.DataSummary.ChannelID=dbo.#PVData.ChannelID and dbo.DataSummary.ManagerID=dbo.#PVData.SharedManagerID and dbo.DataSummary.SummaryDate=dbo.#PVData.CreatDate --统计新的PV信息 insert into dbo.DataSummary select SharedUserID,PlatformID,CreatDate,ChannelID,SharedManagerID,Qty,0,0,0,0,0,0,GETDATE() from dbo.#PVData a where not exists(select * from dbo.DataSummary where dbo.DataSummary.UserID=a.SharedUserID and dbo.DataSummary.PlatformID=a.PlatformID and dbo.DataSummary.ChannelID=a.ChannelID and dbo.DataSummary.ManagerID=a.SharedManagerID and dbo.DataSummary.SummaryDate=a.CreatDate ) --UV --统计的信息存入临时表 select SharedUserID,PlatformID,CONVERT(varchar(100), CreateDate, 111) as CreatDate,ChannelID,SharedManagerID,COUNT(*) as Qty into #UVData from dbo.UVInfo where SharedLevel=1 group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateDate, 111) --更新将统计己存在的信息 update dbo.DataSummary set UV=dbo.#UVData.Qty from #UVData where dbo.DataSummary.UserID=dbo.#UVData.SharedUserID and dbo.DataSummary.PlatformID=dbo.#UVData.PlatformID and dbo.DataSummary.ChannelID=dbo.#UVData.ChannelID and dbo.DataSummary.ManagerID=dbo.#UVData.SharedManagerID and dbo.DataSummary.SummaryDate=dbo.#UVData.CreatDate --统计新的UV信息 insert into dbo.DataSummary select SharedUserID,PlatformID,CreatDate,ChannelID,SharedManagerID,Qty,0,0,0,0,0,0,GETDATE() from dbo.#UVData a where not exists(select * from dbo.DataSummary where dbo.DataSummary.UserID=a.SharedUserID and dbo.DataSummary.PlatformID=a.PlatformID and dbo.DataSummary.ChannelID=a.ChannelID and dbo.DataSummary.ManagerID=a.SharedManagerID and dbo.DataSummary.SummaryDate=a.CreatDate ) commit tran end try begin catch rollback tran end catchend
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。