现在有一表T 里面有这样一些列:rTime datetime
userid int
gamename varchar
scorechanged int我想用一个查询 统计出5天内特定gamename中scorechanged值最大的10个userid 请问应该怎么做?
比如我知道gamename = '扫雷'
userid int
gamename varchar
scorechanged int我想用一个查询 统计出5天内特定gamename中scorechanged值最大的10个userid 请问应该怎么做?
比如我知道gamename = '扫雷'
FROM T
WHERE gamename='扫雷'
AND (rTime BETWEEN DATEADD(DAY,-5,GETDATE()) AND GETDATE())
ORDER BY scorechanged DESC;
--五天内是指最近五天吗?
select top 10 userid from T where gamename='扫雷' order by scorechanged desc
不对哦。。userid 是重复的。。 现在要统计scorechanged 的sum值最大的10个userid
08.10.1 1 扫雷 10
08.10.1 1 扫雷 10
08.10.2 1 扫雷 10
08.10.3 2 扫雷 -10
08.10.3 2 扫雷 -100
08.10.3 2 扫雷 10
.......
这样的
FROM
(
SELECT DISTINCT [userid]
FROM T
WHERE gamename='扫雷'
AND (rTime BETWEEN DATEADD(DAY,-5,GETDATE()) AND GETDATE())
) AS t
ORDER BY scorechanged DESC;
FROM
(
SELECT [userid],SUM(scorechanged) AS scorechanged
FROM T
WHERE gamename='扫雷'
AND (rTime BETWEEN DATEADD(DAY,-5,GETDATE()) AND GETDATE())
GROUOP BY [userid]
) AS t
ORDER BY scorechanged DESC;
SELECT TOP 10 [userid]
FROM
(
SELECT [userid],SUM(scorechanged) AS scorechanged
FROM T
WHERE gamename='扫雷'
AND (rTime BETWEEN DATEADD(DAY,-5,GETDATE()) AND GETDATE())
GROUP BY [userid]
) AS t
ORDER BY scorechanged DESC;