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;