php - 想要显示按月分组的每周最高得分手详细信息

Question

我有一张数据表:

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

我想显示上个月的每周高分详细信息。周从星期日开始。每周显示最多4个最高分。我的数据将以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

Answer 1

您可以使用 YEARWEEK() 命令进行分组。看这个post将星期日作为一周的开始。

因此您可以使用:YEARWEEK(winner.date, 0) 来指定星期。

至于按组选择前 N 行，我找到的最佳引用是 here .向下滚动到该部分:

Select the top N rows from each group

我过去曾使用这篇文章来解决这个确切的问题。

*注意:YEARWEEK() 将处理新的一年，方法是将所有日期都放在指定的日期以开始新的一周到上一年的最后一周......就像你一样/应该期待。

编辑:

这是一个可能的查询(选择前两个以避免 fiddle 示例的一堆数据输入):

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;

sqlFiddle example