取每个分类的当前日期起30天内的数据，按时间倒序，取此分类下的前10条数据，更新为现在时间，只更新日期部分。代码如下

SET NOCOUNT ON

declare @cateid int
declare cursorCateId  cursor for --取出cateid 放入游标
--sql 分类数据大于20
select C.cateid from my_infocate C 
left join my_information I on C.cateid=I.cateid
group by c.cateid
having count(I.infoid)>20

open cursorCateId 
fetch next from cursorCateId into @cateid
while @@fetch_status = 0
begin
    --print @cateid
--------------------------------------------
	DECLARE @_id int, @_updatetime varchar(50)
	DECLARE InfoCursor CURSOR FOR --当前类的信息放入游标中

	--当前分类信息的前30天信息 按时间升序
	select top 10  infoid,updatetime from my_Information
	where DATEDIFF([day], addtime , (select max(addtime) from my_Information) )<30
          and cateid=@cateid
	order by addtime asc

	OPEN InfoCursor
	FETCH NEXT FROM InfoCursor INTO @_id, @_updatetime

	WHILE @@FETCH_STATUS = 0
	BEGIN
	update my_information 
       --  当前的更新时间= 旧更新时间+(当前时间-(当期时间与旧时间的时间差))
       set updatetime=dateadd(dd,DATEDIFF([day], updatetime , GETDATE()),updatetime) 
     where infoid=@_id
		PRINT @_id
	FETCH NEXT FROM InfoCursor INTO @_id, @_updatetime
	END

	CLOSE InfoCursor  --关闭游标//
	DEALLOCATE InfoCursor  --删除游标//
---------------------------------------------
fetch next from cursorCateId into @cateid
end 
close cursorCateId--关闭游标//
deallocate cursorCateid--删除游标//

将以上sql添加到作业里的步骤里的sql区域就可以了。

欢迎批评

发表评论 取消回复