MySQL查询语句：显示最近4周的数据

Question

如何在 MySQL 中编写查询来检索从今天开始的过去 4 周的数据。我用谷歌搜索了查询，但我只得到上周的数据。一周应从今天开始并持续过去 4 周。以下是我的数据

| sales     |   dated           |
---------------------------------
|  12   |   01-08-2022 21:00    |
|  34   |   30-07-2022 21:00    |
|  45   |   29-07-2022 21:00    |
|  67   |   28-07-2022 21:00    |
|  89   |   27-07-2022 21:00    |
|  12   |   26-07-2022 21:00    |
|  34   |   25-07-2022 06:00    |
|  12   |   24-07-2022 21:00    |
|  23   |   23-07-2022 21:00    |
|  11   |   22-07-2022 21:00    |
|  32   |   21-07-2022 21:00    |
|  54   |   20-07-2022 21:00    |
|  65   |   19-07-2022 21:00    |
|  76   |   18-07-2022 15:00    |
|  87   |   17-07-2022 21:00    |
|  98   |   16-07-2022 21:00    |
|  18   |   15-07-2022 21:00    |
|  26   |   14-07-2022 21:00    |
|  25   |   13-07-2022 21:00    |
|  37   |   12-07-2022 21:00    |
|  38   |   11-07-2022 21:00    |
|  36   |   10-07-2022 21:00    |
|  39   |   09-07-2022 21:00    |
|  57   |   08-07-2022 21:00    |
|  45   |   06-07-2022 21:00    |
|  42   |   05-07-2022 21:00    |
|  31   |   04-07-2022 21:00    |
|  33   |   03-07-2022 21:00    |
|  22   |   02-07-2022 21:00    |
|  21   |   01-07-2022 21:00    |

我想要如下所示的输出

Week-1
| sales     |   dated           |
---------------------------------
|  12   |   01-08-2022 21:00    |
|  34   |   30-07-2022 21:00    |
|  45   |   29-07-2022 21:00    |
|  67   |   28-07-2022 21:00    |
|  89   |   27-07-2022 21:00    |
|  12   |   26-07-2022 21:00    |
|  34   |   25-07-2022 06:00    |


Week-2
| sales     |   dated           |
---------------------------------
|  12   |   24-07-2022 21:00    |
|  23   |   23-07-2022 21:00    |
|  11   |   22-07-2022 21:00    |
|  32   |   21-07-2022 21:00    |
|  54   |   20-07-2022 21:00    |
|  65   |   19-07-2022 21:00    |
|  76   |   18-07-2022 15:00    |

Week-4
| sales     |   dated           |
---------------------------------
|  87   |   17-07-2022 21:00    |
|  98   |   16-07-2022 21:00    |
|  18   |   15-07-2022 21:00    |
|  26   |   14-07-2022 21:00    |
|  25   |   13-07-2022 21:00    |
|  37   |   12-07-2022 21:00    |
|  38   |   11-07-2022 21:00    |


Week-3
| sales     |   dated           |
---------------------------------
|  36   |   10-07-2022 21:00    |
|  39   |   09-07-2022 21:00    |
|  57   |   08-07-2022 21:00    |
|  45   |   06-07-2022 21:00    |
|  42   |   05-07-2022 21:00    |
|  31   |   04-07-2022 21:00    |
|  33   |   03-07-2022 21:00    |

请接受我对您宝贵时间的诚挚谢意

Answer 1

您可以将周数作为每行的字段值：

SELECT *, FLOOR(DATEDIFF(CURRENT_DATE(), dated)/7) + 1 AS week_no 
FROM sales
WHERE dated > CURRENT_DATE() - INTERVAL 4 WEEK
ORDER BY dated DESC;