-- 查询时间段内的所有数据,并更新当天的评论数
DECLARE @startday datetime
DECLARE @endday datetime
-- SET @startday = convert(varchar(10),dateadd(dd,-2,getdate()),120) --前天
-- SET @endday = convert(varchar(10),dateadd(dd,-1,getdate()),120) --昨天
SET @startday = '2019-11-09'
SET @endday = '2019-12-01'
--申明游标为Uid
DECLARE @temp bigint
DECLARE ids CURSOR
FOR (SELECT Id FROM craw_product WITH(nolock) WHERE CreateTime > @startday AND CreateTime < @endday )
--打开游标--
OPEN ids
--开始循环游标变量--
FETCH NEXT FROM ids INTO @temp
--返回被 FETCH语句执行的最后游标的状态--
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE craw_product SET CommentCount = (
SELECT COUNT(1) AS Total FROM craw_product_comment WITH(nolock)
WHERE Pid = (SELECT TOP 1 Pid FROM craw_product WITH(nolock) WHERE Id = @temp)
AND DATEDIFF(DAY, CommentTime, (SELECT TOP 1 CreateTime FROM craw_product WITH(nolock) WHERE Id = @temp)) = 0
)
WHERE Id = @temp
FETCH NEXT FROM ids INTO @temp --转到下一个游标
END
--关闭游标
CLOSE ids
--释放游标
DEALLOCATE ids