使用SQL Server代理新建Job

create proc UpdateHits
as

declare @sql varchar(2000)
set @sql = ''

declare @weekday int
declare @wStr varchar(20)
set @wStr=''
--每天0:00执行，此时系统时间已是另外一天，所以-1
set @weekday = datepart(dw,getdate())-1
set @wStr = case @weekday
when 1 then 'sunday'
when 2 then 'monday'
when 3 then 'tuesday'
when 4 then 'wednesday'
when 5 then 'thursday'
when 6 then 'friday'
when 7 then 'saturday'
end

--创建周点击记录(插入日点击表有而周点击表中无的新闻ID,并将其它列置0)
insert into [soco_weekPonitNews](newsId) select distinct newsId from [soco_dayPointNews] where newsId not in (select distinct newsId from [soco_weekPonitNews])

--更新所有新闻周点击
set @sql = 'update [soco_weekPonitNews] set '
set @sql = @sql + @wStr  +' =  (select sum(point) from [soco_dayPointNews] where [soco_dayPointNews].newsId = w.newsId)  from [soco_weekPonitNews] w, [soco_dayPointNews] d where w.newsId = d.newsId '

exec sp_executesql @sql

--将日点击表数据移到点击历史记录表中
insert into soco_newsPointHistory (newsId,point,pDate) select newsId,point,pDate from [soco_dayPointNews]

--清空日点击表
delete from soco_dayPointNews;