在当前月份的几周内将字段总和分组

我有这样的桌子

name | count | date_of_play

其中name仅是varchar,count是整数,并且date是mysql日期。

我试图按当前月份中的星期对表中的行进行整理,然后将每周中所有天的计数求和。

本质上,输出看起来像这样:

Week | Count
1        x
2        y
3        z
4        a

该计数对应于该周发生的总计数。 自然,可能不会总是显示4周,因为我们不会总是在本月底。

目前,我的SQL查询是

Select WEEK(`date_of_play`) Week, SUM(`count`) as Count FROM  table 
WHERE 
WEEK(date_of_play) > (WEEK(CURDATE()-4))
GROUP BY
    WEEK(`date_of_play`) ORDER by MIN(`date_of_play`)

这不会返回任何结果。

任何帮助表示赞赏!

评论
  • oiusto
    oiusto 回复

    您的逻辑需要固定为从周中减去“ 4”。这至少会返回结果:

    SELECT WEEK(date_of_play) as Week, SUM(count) as Count
    FROM table 
    WHERE WEEK(date_of_play) >= WEEK(CURDATE()) - 4
    GROUP BY WEEK(date_of_play)
    ORDER by MIN(date_of_play);
    

    要获取当月的数据,逻辑应类似于:

    SELECT WEEK(date_of_play) as Week, SUM(count) as Count
    FROM table 
    WHERE date_of_play >= CURDATE() - INTERVAL (1 - DAY(CURDATE())) DAY
    GROUP BY WEEK(date_of_play)
    ORDER by MIN(date_of_play)