SQL SERVER 2005 关于统计问题,急啊

发布网友 发布时间：2022-04-11 17:20

我来回答

共3个回答

热心网友 时间：2022-04-11 18:49

SELECT MovieID, sum(PVCount) FROM dbo.Tab_2
WHERE Date BETWEEN DATEADD( d, -7 ,GETDATE()) AND GETDATE()
GROUP BY MovieID
这样就可以得出一周内各视频的查看次数，之后再根据这个结果集更新Tab_1就可以了。

完整的如下：
UPDATE a
SET a.PVCountWeekly = b.WeeklyCount
from Tab_1 a
INNER JOIN
(
SELECT MovieID, sum(PVCount) as WeeklyCount FROM dbo.Tab_2
WHERE Date BETWEEN DATEADD( d, -7 ,GETDATE()) AND GETDATE()
GROUP BY MovieID
) b
ON a.MovieID = b.MovieID

热心网友 时间：2022-04-11 20:07

最近一周是指什么时间范围? 从今天起向后退七天, 还是今天所在的这一周的从周一到周日?
如果是第一种情况:
Update TAB_1 set PVCountWeekly=(
select sum(PVCount) from TAB_2 where MovieID=TAB_1.MovieID AND DATEDIFF(d, [Date], getdate())<=7)

第二种情况是:
Update TAB_1 set PVCountWeekly=(
select sum(PVCount) from TAB_2 where MovieID=TAB_1.MovieID AND DATEPART(ww, [Date])=DATEPART(ww, getdate()))

热心网友 时间：2022-04-11 21:42

VFP 代码参考：
SELECT MovieID, SUM(PVCount) AS "PVCountWeekly" FROM TAB_2 WHERE dDate BETWEEN (DATE()-DOW(DATE())+2) AND (DATE()+(7-DOW(DATE()))+1) GROUP BY TAB_2.MovieID INTO CURSOR curWeek
SELECT TAB_1.MovieID, TAB_1.PVCountAll, curWeek.PVCountWeekly FROM TAB_1 LEFT OUTER JOIN curWeek ON TAB_1.MovieID = curWeek.MovieID INTO CURSOR curRet

SELECT curRet
BROWSE