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

Question

我需要一个查询，该查询允许我从 MySQL 表中选择过去 4 周的数据，但输出为从 4 周前的星期一开始的天数，无论当前是星期几。

数据库有一个用户表和一个事件表，其中具有用户 ID 的 FK。带有 FK 到 ActivityID 的 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 周，并且单独的查询可能无法满足要求。

不知道从哪里开始。

Answer 1

假设您的时间戳列名为 timestamp，您可以:

SELECT CONCAT(YEAR(时间戳), '/', WEEK(时间戳)) AS 周

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