mysql - 获取每周数据

Question

我有一个表格如下

CREATE TABLE messages  ( 
id          int(10) UNSIGNED AUTO_INCREMENT NOT NULL,
messagetext text NOT NULL,
created_at  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY(id)

)

我想做的是获取两周的数据，当前周和前一周，按天排序。例如，本周和上周在星期日、星期一、星期二、星期三...创建了多少条消息。

我确实有如下查询

SELECT
    COUNT(id) AS 'Count' 
FROM
    'messages'
WHERE
    (WEEK(created_at) = WEEK(NOW())) 
GROUP BY
    WEEKDAY(created_at) 
ORDER BY
    week(created_at) ASC,
    weekday(created_at) ASC

但我不知道如何明智地对它进行排序。例如，上面的查询将列出我今天或昨天得到了多少，但这不是我想要的。我想要的是让计数日和周都明智。

这是我想要的

如果我对我所面临的情况足够清楚，请告诉我。

更新:有关详细信息，请参阅下面 Juan 的问题

如果当前天数少于 14 天，在这种情况下，脚本应该返回前一周的天数，而不是前一周的前一周。 Juan 是这样解释的(假设今天是星期五。如果你倒数 14 天，你会得到这周的星期一到星期五。上一周的星期一到星期日......以及上一周的星期五到星期日。不确定这是否是你的结果想要)

问候

加根

Answer 1

使用条件 SUM

SELECT week(created_at) week_id,
       SUM( IF(weekday(created_at) = 0, 1, 0) ) as monday,
       SUM( IF(weekday(created_at) = 1, 1, 0) ) as tuesday,
       SUM( IF(weekday(created_at) = 2, 1, 0) ) as wednesday,
       SUM( IF(weekday(created_at) = 3, 1, 0) ) as thursday,
       SUM( IF(weekday(created_at) = 4, 1, 0) ) as friday,
       SUM( IF(weekday(created_at) = 5, 1, 0) ) as saturday,
       SUM( IF(weekday(created_at) = 6, 1, 0) ) as sunday
FROM  'messages'
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 14 DAY)
  AND (      week_id = WEEK( NOW() ) 
         OR  week_id = WEEK( DATE_SUB(NOW(), INTERVAL 7 DAY) 
      )
GROUP BY
    week_id
ORDER BY
    week_id