如果您对我的评论的回答是肯定的话,请查看以下代码:)由于您的数据全部在2012年和11月,我花了一天.
查询:
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
;
结果:
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或dense_rank.
另请注意,我使用的数据是前2名而不是前3名.你可以相应改变.
猜猜看,答案比第一次数据给出的第一印象简单10倍….
查询到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
这里是我使用的数据的直接计算的快照.