mysql用户分为_mysql – 为每个用户选择每天最高的3个分数

如果您对我的评论的回答是肯定的话,请查看以下代码:)由于您的数据全部在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

这里是我使用的数据的直接计算的快照.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值