php - 按星期分组按天获取最近4周的记录

我需要一个查询，该查询将允许我从MySQL表中选择最近4周的数据，但是输出是从4周前的星期一开始的天数，而与一周中的当前日期无关。

DB有一个用户表，一个活动表具有一个FID的userID。
具有活动ID的FK的activityStats表

该查询将用于创建类似于以下内容的数组：

$array = [
   "userid" => "userid",
   "dateAdded" => "dateAdded",
   "week1" => $array = [
      "monday" => $array = [
         "activityid" => "1",
         "time" => "21",
         ...
      ]
      "tuesday" => $array = [
         "activityid" => "2",
         "time" => "16",
         ...
      ]
      "wednesday" => $array = [
         "activityid" => "3",
         "time" => "16",
         ...
      ]
      ...
   "week2" => $array = [
      "monday" => $array = [
         "activityid" => "8",
         "time" => "21",
         ...
      ]
      "tuesday" => $array = [
         "activityid" => "9",
         "time" => "16",
         ...
      ]
      "wednesday" => $array = [
         "activityid" => "10",
         "time" => "16",
         ...
      ]
      ...
   ]
   ...
];

我可以创建多个查询来解决问题，但是可能需要10周或52周的时间，而单独的查询可能无法解决问题。

不知道从哪里开始。

最佳答案

假设您的时间戳列称为timestamp，则可以：

SELECT CONCAT(YEAR(timestamp), '/', WEEK(timestamp)) AS week

然后检索特定范围，按周排序等。

关于php - 按星期分组按天获取最近4周的记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32835453/