如果您对我的评论的回答是,请看一下下面的代码yes
:) 由于你的数据都是2012年的,而且是11月,所以我花了一天的时间。
Query:
select y.id, y.userid, y.score, y.datestamp
from (select id, userid, score, datestamp
from scores
group by day(datestamp)) as y
where (select count(*)
from (select id, userid, score, datestamp
from scores group by day(datestamp)) as x
where y.score >= x.score
and y.userid = x.userid
) =1 -- Top 3rd, 2nd, 1st
order by y.score desc
;
Results:
ID USERID SCORE DATESTAMP
8 2 8.5 December, 07 2012 00:00:00+0000
20 3 6 December, 08 2012 00:00:00+0000
1 1 5 December, 06 2012 00:00:00+0000
根据您对问题的最新更新。
如果您需要按年/月/日为每个用户提供一些然后找到最高的,您可以简单地添加聚合函数,例如sum
对于上面的查询。我重复一遍,因为您的样本数据只有一年,所以没有按年或按月的点组。这就是为什么我花了一天时间。
select y.id, y.userid, y.score, y.datestamp
from (select id, userid, sum(score) as score,
datestamp
from scores
group by userid, day(datestamp)) as y
where (select count(*)
from (select id, userid, sum(score) as score
, datestamp
from scores
group by userid, day(datestamp)) as x
where y.score >= x.score
and y.userid = x.userid
) =1 -- Top 3rd, 2nd, 1st
order by y.score desc
;
基于总和的结果:
ID USERID SCORE DATESTAMP
1 1 47.5 December, 06 2012 00:00:00+0000
8 2 16 December, 07 2012 00:00:00+0000
20 3 6 December, 08 2012 00:00:00+0000
更新了新的源数据示例
西蒙,请看一下我自己的样本。当你的数据发生变化时,我使用了我的数据。
这是参考。我用过纯的ansi
风格没有任何over partition
or dense_rank
。
另请注意,我使用的数据是获得前 2 名而不是前 3 名的分数。您可以相应地更改。
你猜怎么着,答案比你的第一个数据给人的第一印象简单 10 倍......
SQLFIDDLE
查询1:
-- 每天用户排名前 2 的总和
SELECT userid, sum(Score), datestamp
FROM scores t1
where 2 >=
(SELECT count(*)
from scores t2
where t1.score <= t2.score
and t1.userid = t2.userid
and day(t1.datestamp) = day(t2.datestamp)
order by t2.score desc)
group by userid, datestamp
;
查询 1 的结果:
USERID SUM(SCORE) DATESTAMP
1 70 December, 06 2012 00:00:00+0000
1 30 December, 07 2012 00:00:00+0000
2 22 December, 06 2012 00:00:00+0000
2 25 December, 07 2012 00:00:00+0000
3 30 December, 06 2012 00:00:00+0000
3 30 December, 07 2012 00:00:00+0000
最终查询:
-- 对于所有两天用户排名前 2 的总和
SELECT userid, sum(Score)
FROM scores t1
where 2 >=
(SELECT count(*)
from scores t2
where t1.score <= t2.score
and t1.userid = t2.userid
and day(t1.datestamp) = day(t2.datestamp)
order by t2.score desc)
group by userid
;
最终结果:
USERID SUM(SCORE)
1 100
2 47
3 60
这是我使用的数据直接计算的快照。