我有一个数据表:
id | name | userid | score | date |
--------------------------------------------------------
1 | john | 4 | 233 | 2014-02-02
2 | mary | 5 | 1256 | 2013-02-05
3 | john | 6 | 100 | 2013-03-08
4 | elvis | 7 | 123 | 2013-03-04
5 | john | 2 | 1234 | 2013-03-02我想从周日开始展示上一次month.week的每周高分细节。每周显示最多4个最高的scorer.My数据将以json格式输出。
目前,我的查询输出仅显示上周的结果。
"weekly_winners":[
{
"id":"2",
"score":"1256"
},
{
"id":"1",
"score":"233"
}
],但我想展示的是
"weekly_winners":[
"week":"1"
[
{
"id":"2",
"score":"1256"
},
{
"id":"1",
"score":"233"
}
],
"week":"2"
[
{
"id":"2",
"score":"1256"
},
{
"id":"1",
"score":"233"
}
],
],我当前的查询是:
SELECT winner.id,winner.score
FROM `winner` AS winner
WHERE winner.date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND winner.date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
ORDER BY winner.score DESC
LIMIT 4发布于 2014-03-13 03:57:27
您可以使用YEARWEEK()命令进行分组。请看这个post,让星期天成为一周的开始。
因此,您可以使用:YEARWEEK(winner.date, 0)来指定星期。
至于按组选择前N行,我在执行此操作时找到的最佳参考资料是here。向下滚动到该部分:
从每个组中选择前N行
在过去,我已经使用这篇文章来解决这个问题。
*注意:YEARWEEK()将通过将指定开始新一周的日期的所有日期放入前一年的最后一周来处理新的一年...正如您所期望的那样。
编辑:
下面是一个可能的查询(选择前两个以避免小提琴示例中的大量数据输入):
SELECT winner.id,winner.score, YEARWEEK(winner.date, 0)
FROM `winner` AS winner
WHERE (
SELECT count(*)
FROM winner as w
WHERE YEARWEEK(w.date, 0) = YEARWEEK(winner.date, 0)
AND w.score >= winner.score
) <= 2;https://stackoverflow.com/questions/22361987
复制相似问题