Edit D:\SQL20160530-20170910\16.12.05.28_0005_Pub_New

Edit D:\SQL20160530-20170910\16.12.05.28_0005_Pub_New_Add1.sql

/**
 * ?? 
       1?????????????PvHistory? 
       2??????????? 
       3?????PV
       
   ???
       pvTarget?Application?Album?Video?JiaoYanQuan?PublicClass?Subjecrt?Meeting?VideoAsk?Guest
       pvType?1???? 2??? 3????4?????
       
   ?????
		Create by Qin at 2016-12-07
 */
create procedure PV_New
as
begin
	-- 1 :????????????PvHistory
	INSERT INTO Pub_PVHistory(Guid,Version,pvType,pvTarget,pvGuid,pvIp,userGuid,createTime) 
	SELECT REPLACE(NEWID(),'-',''),1,pvType,pvTarget,pvGuid,pvIp,userGuid,createTime FROM Pub_PVHistoryToday WHERE createTime < CONVERT(varchar(10),getdate(),120)
	 		 
	-- 2 :??????????
	DELETE FROM Pub_PVHistoryToday WHERE createTime < CONVERT(varchar(10),getdate(),120)
	
	-- 3 :?? PV
	-- pub_pv ??? pv_type = 4 , ??? PVIP?UserGuid ????????? 0:(????)  1:?????????? pub_pv ?
	select * 
	into #tmp_pv 
	from (SELECT pvType,pvTarget,pvGuid,pvIp,userGuid,createTime FROM Pub_PVHistoryToday WHERE pvType = 4 
		  UNION ALL
		  SELECT pvType,pvTarget,pvGuid,pvIp,userGuid,createTime FROM Pub_PVHistory WHERE pvType = 4) a
		 	 
	-- ????  0:(????)
	if exists (select 1 from Pub_PV where Guid = '0')
	begin
		update Pub_PV 
		set DayViewCount = (select COUNT(*) from (select  distinct pvIP, ISNULL(userGuid,'') userGuid from #tmp_pv where CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) a)
		where Guid = '0'
	end
	else
	begin
		insert into Pub_PV(Guid,Vserion,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount)
		select '0',1,count(*),0,0,0,0 from 
		(select  distinct pvIP, ISNULL(userGuid,'') userGuid from #tmp_pv where CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) a
	end
	-- ????  1:?????	
	if exists (select 1 from Pub_PV where Guid = '1')
	begin
		update Pub_PV 
		set DayViewCount = (select COUNT(*) from #tmp_pv where CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) 
		where Guid = '1'
	end
	else
	begin
		insert into Pub_PV(Guid,Vserion,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount)
		select '1',1,count(*),0,0,0,0 from #tmp_pv where CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120) 
	end
	
	-- ????  0:(????)
	update Pub_PV 
	set WeekViewCount = (select COUNT(*) from (select  distinct pvIP, ISNULL(userGuid,'') userGuid from #tmp_pv where CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DATEADD(WEEK,-1,getdate()),120)) a)
	where Guid = '0'
	-- ????  1:?????	
	update Pub_PV 
	set WeekViewCount = (select COUNT(*) from #tmp_pv where CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DATEADD(WEEK,-1,getdate()),120)) 
	where Guid = '1'
	
	-- ????  0:(????)
	update Pub_PV 
	set MonthViewCount = (select COUNT(*) from (select  distinct pvIP, ISNULL(userGuid,'') userGuid from #tmp_pv where CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DATEADD(MONTH,-1,getdate()),120)) a)
	where Guid = '0'
	-- ????  1:?????	
	update Pub_PV 
	set MonthViewCount = (select COUNT(*) from #tmp_pv where CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DATEADD(MONTH,-1,getdate()),120)) 
	where Guid = '1'
	
	-- ????  0:(????)
	update Pub_PV 
	set YearViewCount = (select COUNT(*) from (select  distinct pvIP, ISNULL(userGuid,'') userGuid from #tmp_pv where CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DATEADD(YEAR,-1,getdate()),120)) a)
	where Guid = '0'
	-- ????  1:?????	
	update Pub_PV 
	set YearViewCount = (select COUNT(*) from #tmp_pv where CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DATEADD(YEAR,-1,getdate()),120)) 
	where Guid = '1'
	
	-- ????  0:(????)
	update Pub_PV 
	set AllViewCount = (select COUNT(*) from (select  distinct pvIP, ISNULL(userGuid,'') userGuid from #tmp_pv ) a)
	where Guid = '0'
	-- ????  1:?????
	update Pub_PV 
	set AllViewCount = (select COUNT(*) from #tmp_pv ) 
	where Guid = '1'	
	
	drop table #tmp_pv

-- ??????????????
	create table #count
	(
		Guid nvarchar(64) not null,
		DayViewCount int default 0,
		WeekViewCount int default 0,
		MonthViewCount int default 0,
		YearViewCount int default 0,
		AllViewCount int default 0,
		
		DayCommentCount int default 0,
		WeekCommentCount int default 0,
		MonthCommentCount int default 0,
		YearCommentCount int default 0,
		AllCommentCount int default 0,
		
		DayCollectCount int default 0,
		WeekCollectCount int default 0,
		MonthCollectCount int default 0,
		YearCollectCount int default 0,
		AllCollectCount int default 0,
	)
	
	
	-- 4 :?? Pub_AlbumPV
	-- ?? Pub_PVHistory.pvTarget = Album
	select * 
	into #tmp_album 
	from (SELECT pvType,pvGuid,createTime FROM Pub_PVHistoryToday WHERE PVTarget = 'Album' 
		  UNION ALL
		  SELECT pvType,pvGuid,createTime FROM Pub_PVHistory WHERE PVTarget = 'Album' ) a
	
	truncate table #count
	insert into #count(Guid,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount)
	select pvGuid,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearViewCount,
			SUM( case when(pvType = 1) then 1 else 0 end) AllViewCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCommentCount,
			SUM( case when(pvType = 2) then 1 else 0 end) AllCommentCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCollectCount,
			SUM( case when(pvType = 3) then 1 else 0 end) AllCollectCount
	from #tmp_album
	group by pvGuid
	
	update Pub_AlbumPV set DayViewCount=b.DayViewCount, WeekViewCount=b.WeekViewCount, MoonViewCount=b.MonthViewCount, YearViewCount=b.YearViewCount, AllViewCount=b.AllViewCount,
		DayCommentCount=b.DayCommentCount, WeekCommentCount=b.WeekCommentCount, MoonCommentCount=b.MonthCommentCount, YearCommentCount=b.YearCommentCount, AllCommentCount=b.AllCommentCount,
		DayCollectCount=b.DayCollectCount, WeekCollectCount=b.WeekCollectCount, MoonCollectCount=b.MonthCollectCount, YearCollectCount=b.YearCollectCount, AllCollectCount=b.AllCollectCount
	from Pub_AlbumPV a inner join #count b on a.Guid = b.guid
	
	delete #count from #count a, Pub_AlbumPV b where a.guid = b.Guid
	
	insert into Pub_AlbumPV(Guid,Version,DayViewCount,WeekViewCount,MoonViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MoonCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MoonCollectCount,YearCollectCount,AllCollectCount) 
	select Guid,1,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount
	from #count
	
	drop table #tmp_album
	UPDATE Pub_Album
		   SET 
			   VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_AlbumPV WITH(NOLOCK) WHERE (Guid = Pub_Album.guid)) ,0), 
			   CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_AlbumPV WITH(NOLOCK) WHERE (Guid = Pub_Album.guid)), 0), 
			   CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_AlbumPV WITH(NOLOCK) WHERE (Guid = Pub_Album.guid)), 0)		
   
   	-- 5 :?? Pub_ApplicationPV
	-- ?? Pub_PVHistory.pvTarget = Application
	select * 
	into #tmp_application 
	from (SELECT pvType,pvGuid,createTime FROM Pub_PVHistoryToday WHERE PVTarget = 'Application' 
		  UNION ALL
		  SELECT pvType,pvGuid,createTime FROM Pub_PVHistory WHERE PVTarget = 'Application' ) a
	
	truncate table #count
	insert into #count(Guid,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount)
	select pvGuid,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearViewCount,
			SUM( case when(pvType = 1) then 1 else 0 end) AllViewCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCommentCount,
			SUM( case when(pvType = 2) then 1 else 0 end) AllCommentCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCollectCount,
			SUM( case when(pvType = 3) then 1 else 0 end) AllCollectCount
	from #tmp_application
	group by pvGuid
	
	update Pub_ApplicationPV set DayViewCount=b.DayViewCount, WeekViewCount=b.WeekViewCount, MoonViewCount=b.MonthViewCount, YearViewCount=b.YearViewCount, AllViewCount=b.AllViewCount,
		DayCommentCount=b.DayCommentCount, WeekCommentCount=b.WeekCommentCount, MoonCommentCount=b.MonthCommentCount, YearCommentCount=b.YearCommentCount, AllCommentCount=b.AllCommentCount,
		DayCollectCount=b.DayCollectCount, WeekCollectCount=b.WeekCollectCount, MoonCollectCount=b.MonthCollectCount, YearCollectCount=b.YearCollectCount, AllCollectCount=b.AllCollectCount
	from Pub_ApplicationPV a inner join #count b on a.Guid = b.guid
	
	delete #count from #count a, Pub_ApplicationPV b where a.guid = b.Guid
	
	insert into Pub_ApplicationPV(Guid,Version,DayViewCount,WeekViewCount,MoonViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MoonCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MoonCollectCount,YearCollectCount,AllCollectCount) 
	select Guid,1,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount
	from #count
	
	drop table #tmp_application	
	UPDATE Pub_Application
		   SET 
			   VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_ApplicationPV WITH(NOLOCK) WHERE (Guid = Pub_Application.guid)) ,0), 
			   CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_ApplicationPV WITH(NOLOCK) WHERE (Guid = Pub_Application.guid)), 0), 
			   CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_ApplicationPV WITH(NOLOCK) WHERE (Guid = Pub_Application.guid)), 0)		
	
   	-- 6 :?? Pub_JYQPV
	-- ?? Pub_PVHistory.pvTarget = JiaoYanQuan
	select * 
	into #tmp_JiaoYanQuan
	from (SELECT pvType,pvGuid,createTime FROM Pub_PVHistoryToday WHERE PVTarget = 'JiaoYanQuan' 
		  UNION ALL
		  SELECT pvType,pvGuid,createTime FROM Pub_PVHistory WHERE PVTarget = 'JiaoYanQuan' ) a
	
	truncate table #count
	insert into #count(Guid,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount)
	select pvGuid,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearViewCount,
			SUM( case when(pvType = 1) then 1 else 0 end) AllViewCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCommentCount,
			SUM( case when(pvType = 2) then 1 else 0 end) AllCommentCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCollectCount,
			SUM( case when(pvType = 3) then 1 else 0 end) AllCollectCount
	from #tmp_JiaoYanQuan
	group by pvGuid
	
	update Pub_JYQPV set DayViewCount=b.DayViewCount, WeekViewCount=b.WeekViewCount, MoonViewCount=b.MonthViewCount, YearViewCount=b.YearViewCount, AllViewCount=b.AllViewCount,
		DayCommentCount=b.DayCommentCount, WeekCommentCount=b.WeekCommentCount, MoonCommentCount=b.MonthCommentCount, YearCommentCount=b.YearCommentCount, AllCommentCount=b.AllCommentCount,
		DayCollectCount=b.DayCollectCount, WeekCollectCount=b.WeekCollectCount, MoonCollectCount=b.MonthCollectCount, YearCollectCount=b.YearCollectCount, AllCollectCount=b.AllCollectCount
	from Pub_JYQPV a inner join #count b on a.Guid = b.guid
	
	delete #count from #count a, Pub_JYQPV b where a.guid = b.Guid
	
	insert into Pub_JYQPV(Guid,Version,DayViewCount,WeekViewCount,MoonViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MoonCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MoonCollectCount,YearCollectCount,AllCollectCount) 
	select Guid,1,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount
	from #count
	
	drop table #tmp_JiaoYanQuan
	UPDATE Pub_JiaoYanQuan
		   SET 
			   VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_JYQPV WITH(NOLOCK) WHERE (Guid = Pub_JiaoYanQuan.guid)) ,0), 
			   CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_JYQPV WITH(NOLOCK) WHERE (Guid = Pub_JiaoYanQuan.guid)), 0), 
			   CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_JYQPV WITH(NOLOCK) WHERE (Guid = Pub_JiaoYanQuan.guid)), 0)

-- 7 :?? Pub_MeetingPV
	-- ?? Pub_PVHistory.pvTarget = Meeting
	select * 
	into #tmp_Meeting
	from (SELECT pvType,pvGuid,createTime FROM Pub_PVHistoryToday WHERE PVTarget = 'Meeting' 
		  UNION ALL
		  SELECT pvType,pvGuid,createTime FROM Pub_PVHistory WHERE PVTarget = 'Meeting' ) a
	
	truncate table #count
	insert into #count(Guid,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount)
	select pvGuid,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearViewCount,
			SUM( case when(pvType = 1) then 1 else 0 end) AllViewCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCommentCount,
			SUM( case when(pvType = 2) then 1 else 0 end) AllCommentCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCollectCount,
			SUM( case when(pvType = 3) then 1 else 0 end) AllCollectCount
	from #tmp_Meeting
	group by pvGuid
	
	update Pub_MeetingPV set DayViewCount=b.DayViewCount, WeekViewCount=b.WeekViewCount, MoonViewCount=b.MonthViewCount, YearViewCount=b.YearViewCount, AllViewCount=b.AllViewCount,
		DayCommentCount=b.DayCommentCount, WeekCommentCount=b.WeekCommentCount, MoonCommentCount=b.MonthCommentCount, YearCommentCount=b.YearCommentCount, AllCommentCount=b.AllCommentCount,
		DayCollectCount=b.DayCollectCount, WeekCollectCount=b.WeekCollectCount, MoonCollectCount=b.MonthCollectCount, YearCollectCount=b.YearCollectCount, AllCollectCount=b.AllCollectCount
	from Pub_MeetingPV a inner join #count b on a.Guid = b.guid
	
	delete #count from #count a, Pub_MeetingPV b where a.guid = b.Guid
	
	insert into Pub_MeetingPV(Guid,Version,DayViewCount,WeekViewCount,MoonViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MoonCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MoonCollectCount,YearCollectCount,AllCollectCount) 
	select Guid,1,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount
	from #count
	
	drop table #tmp_Meeting
	UPDATE Pub_Meeting
		   SET 
			   VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_MeetingPV WITH(NOLOCK) WHERE (Guid = Pub_Meeting.guid)) ,0), 
			   CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_MeetingPV WITH(NOLOCK) WHERE (Guid = Pub_Meeting.guid)), 0), 
			   CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_MeetingPV WITH(NOLOCK) WHERE (Guid = Pub_Meeting.guid)), 0)

-- 8 :?? Pub_PublicClassPV
	-- ?? Pub_PVHistory.pvTarget = PublicClass
	select * 
	into #tmp_PublicClass
	from (SELECT pvType,pvGuid,createTime FROM Pub_PVHistoryToday WHERE PVTarget = 'PublicClass' 
		  UNION ALL
		  SELECT pvType,pvGuid,createTime FROM Pub_PVHistory WHERE PVTarget = 'PublicClass' ) a
	
	truncate table #count
	insert into #count(Guid,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount)
	select pvGuid,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearViewCount,
			SUM( case when(pvType = 1) then 1 else 0 end) AllViewCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCommentCount,
			SUM( case when(pvType = 2) then 1 else 0 end) AllCommentCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCollectCount,
			SUM( case when(pvType = 3) then 1 else 0 end) AllCollectCount
	from #tmp_PublicClass
	group by pvGuid
	
	update Pub_PublicClassPV set DayViewCount=b.DayViewCount, WeekViewCount=b.WeekViewCount, MoonViewCount=b.MonthViewCount, YearViewCount=b.YearViewCount, AllViewCount=b.AllViewCount,
		DayCommentCount=b.DayCommentCount, WeekCommentCount=b.WeekCommentCount, MoonCommentCount=b.MonthCommentCount, YearCommentCount=b.YearCommentCount, AllCommentCount=b.AllCommentCount,
		DayCollectCount=b.DayCollectCount, WeekCollectCount=b.WeekCollectCount, MoonCollectCount=b.MonthCollectCount, YearCollectCount=b.YearCollectCount, AllCollectCount=b.AllCollectCount
	from Pub_PublicClassPV a inner join #count b on a.Guid = b.guid
	
	delete #count from #count a, Pub_PublicClassPV b where a.guid = b.Guid
	
	insert into Pub_PublicClassPV(Guid,Version,DayViewCount,WeekViewCount,MoonViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MoonCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MoonCollectCount,YearCollectCount,AllCollectCount) 
	select Guid,1,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount
	from #count
	
	drop table #tmp_PublicClass
	UPDATE Pub_PublicClass
		   SET 
			   VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_PublicClassPV WITH(NOLOCK) WHERE (Guid = Pub_PublicClass.guid)) ,0), 
			   CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_PublicClassPV WITH(NOLOCK) WHERE (Guid = Pub_PublicClass.guid)), 0), 
			   CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_PublicClassPV WITH(NOLOCK) WHERE (Guid = Pub_PublicClass.guid)), 0)

-- 9 :?? Pub_SubjectPV
	-- ?? Pub_PVHistory.pvTarget = Subject
	select * 
	into #tmp_Subject
	from (SELECT pvType,pvGuid,createTime FROM Pub_PVHistoryToday WHERE PVTarget = 'Subject' 
		  UNION ALL
		  SELECT pvType,pvGuid,createTime FROM Pub_PVHistory WHERE PVTarget = 'Subject' ) a
	
	truncate table #count
	insert into #count(Guid,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount)
	select pvGuid,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearViewCount,
			SUM( case when(pvType = 1) then 1 else 0 end) AllViewCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCommentCount,
			SUM( case when(pvType = 2) then 1 else 0 end) AllCommentCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCollectCount,
			SUM( case when(pvType = 3) then 1 else 0 end) AllCollectCount
	from #tmp_Subject
	group by pvGuid
	
	update Pub_SubjectPV set DayViewCount=b.DayViewCount, WeekViewCount=b.WeekViewCount, MoonViewCount=b.MonthViewCount, YearViewCount=b.YearViewCount, AllViewCount=b.AllViewCount,
		DayCommentCount=b.DayCommentCount, WeekCommentCount=b.WeekCommentCount, MoonCommentCount=b.MonthCommentCount, YearCommentCount=b.YearCommentCount, AllCommentCount=b.AllCommentCount,
		DayCollectCount=b.DayCollectCount, WeekCollectCount=b.WeekCollectCount, MoonCollectCount=b.MonthCollectCount, YearCollectCount=b.YearCollectCount, AllCollectCount=b.AllCollectCount
	from Pub_SubjectPV a inner join #count b on a.Guid = b.guid
	
	delete #count from #count a, Pub_SubjectPV b where a.guid = b.Guid
	
	insert into Pub_SubjectPV(Guid,Version,DayViewCount,WeekViewCount,MoonViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MoonCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MoonCollectCount,YearCollectCount,AllCollectCount) 
	select Guid,1,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount
	from #count
	
	drop table #tmp_Subject
	UPDATE Pub_Subject
		   SET 
			   VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_SubjectPV WITH(NOLOCK) WHERE (Guid = Pub_Subject.guid)) ,0), 
			   CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_SubjectPV WITH(NOLOCK) WHERE (Guid = Pub_Subject.guid)), 0), 
			   CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_SubjectPV WITH(NOLOCK) WHERE (Guid = Pub_Subject.guid)), 0)

-- 10 :?? Pub_VideoPV
	-- ?? Pub_PVHistory.pvTarget = Video
	select * 
	into #tmp_Video
	from (SELECT pvType,pvGuid,createTime FROM Pub_PVHistoryToday WHERE PVTarget = 'Video' 
		  UNION ALL
		  SELECT pvType,pvGuid,createTime FROM Pub_PVHistory WHERE PVTarget = 'Video' ) a
	
	truncate table #count
	insert into #count(Guid,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount)
	select pvGuid,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthViewCount,
			SUM( case when((pvType = 1) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearViewCount,
			SUM( case when(pvType = 1) then 1 else 0 end) AllViewCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCommentCount,
			SUM( case when((pvType = 2) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCommentCount,
			SUM( case when(pvType = 2) then 1 else 0 end) AllCommentCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) = CONVERT(varchar(10),getdate(),120)) ) then 1 else 0 end) DayCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(week,-1,getdate()),120)) ) then 1 else 0 end) WeekCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(MONTH,-1,getdate()),120)) ) then 1 else 0 end) MonthCollectCount,
			SUM( case when((pvType = 3) and (CONVERT(varchar(10),createTime,120) >= CONVERT(varchar(10),DateAdd(YEAR,-1,getdate()),120)) ) then 1 else 0 end) YearCollectCount,
			SUM( case when(pvType = 3) then 1 else 0 end) AllCollectCount
	from #tmp_Video
	group by pvGuid
	
	update Pub_VideoPV set DayViewCount=b.DayViewCount, WeekViewCount=b.WeekViewCount, MoonViewCount=b.MonthViewCount, YearViewCount=b.YearViewCount, AllViewCount=b.AllViewCount,
		DayCommentCount=b.DayCommentCount, WeekCommentCount=b.WeekCommentCount, MoonCommentCount=b.MonthCommentCount, YearCommentCount=b.YearCommentCount, AllCommentCount=b.AllCommentCount,
		DayCollectCount=b.DayCollectCount, WeekCollectCount=b.WeekCollectCount, MoonCollectCount=b.MonthCollectCount, YearCollectCount=b.YearCollectCount, AllCollectCount=b.AllCollectCount
	from Pub_VideoPV a inner join #count b on a.Guid = b.guid
	
	delete #count from #count a, Pub_VideoPV b where a.guid = b.Guid
	
	insert into Pub_VideoPV(Guid,Version,DayViewCount,WeekViewCount,MoonViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MoonCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MoonCollectCount,YearCollectCount,AllCollectCount) 
	select Guid,1,DayViewCount,WeekViewCount,MonthViewCount,YearViewCount,AllViewCount,
		DayCommentCount,WeekCommentCount,MonthCommentCount,YearCommentCount,AllCommentCount,
		DayCollectCount,WeekCollectCount,MonthCollectCount,YearCollectCount,AllCollectCount
	from #count
	
	drop table #tmp_Video

drop table #count
	UPDATE Pub_Video
		   SET 
			   VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_VideoPV WITH(NOLOCK) WHERE (Guid = Pub_Video.guid)) ,0), 
			   CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_VideoPV WITH(NOLOCK) WHERE (Guid = Pub_Video.guid)), 0), 
			   CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_VideoPV WITH(NOLOCK) WHERE (Guid = Pub_Video.guid)), 0)
	
	
	-- 11.?? Pub_PVDay
	select CONVERT(varchar(10),createTime,120) AS DAY, pvTarget, pvType,COUNT(*) AS cnt 
	into #tmp_PVDay
	FROM Pub_PVHistory 
	where CONVERT(varchar(10),createTime,120) >= (select convert(varchar(10),isnull(MAX(Day),'2000-01-01'),120) from Pub_PVDay)
	group by CONVERT(varchar(10),createTime,120), pvTarget, pvType
	
	update Pub_PVDay set DayCount = b.cnt
	from Pub_PVDay a inner join #tmp_PVDay b on a.Day = b.DAY and a.PVTarget = b.pvTarget and a.PVType = b.pvType
	
	delete a from #tmp_PVDay a, Pub_PVDay b where a.Day = b.DAY and a.PVTarget = b.pvTarget and a.PVType = b.pvType
	
	insert into Pub_PVDay(Guid,Version,Day,PVTarget,PVType,DayCount)
	select REPLACE(NEWID(),'-',''),1,DAY,pvTarget,pvType,cnt FROM #tmp_PVDay
	
	drop table #tmp_PVDay

end

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de