mysql - 如何使用 MySQL 在两个日期之间获取该周的周数和日期

Question

我正在使用 MySQL 并且我有两个日期“从日期”和“到日期”，并且基于这些日期，我想获取“到”和“从”日期之间的那一周的周数和日期。

我尝试了以下 mysql 查询。

SELECT count(*) as count,
       CONCAT(DATE_FORMAT(DATE_ADD(FROM_DAYS(TO_DAYS(FROM_UNIXTIME(`webform_submissions`.`submitted`)) -MOD(TO_DAYS(FROM_UNIXTIME(`webform_submissions`.`submitted`)) -1, 7)),INTERVAL -6 DAY),'%M %d'), ' - ' ,DATE_FORMAT(FROM_DAYS(TO_DAYS(FROM_UNIXTIME(`webform_submissions`.`submitted`)) -MOD(TO_DAYS(FROM_UNIXTIME(`webform_submissions`.`submitted`)) -1, 7)),'%M %d')) as date , 
       CONCAT(YEAR(FROM_UNIXTIME(`webform_submissions`.`submitted`)), '/', WEEK(FROM_UNIXTIME(`webform_submissions`.`submitted`))) as week
FROM `webform_submissions` 
where `webform_submissions`.`nid` = 121 
  AND DATE_FORMAT(FROM_UNIXTIME(`webform_submissions`.`submitted`), '%Y-%m-%d') between '2019-11-01' and '2019-12-03'
GROUP BY week 
ORDER BY `webform_submissions`.`submitted` ASC

根据上述查询显示以下结果。

但它似乎给出了错误的结果，因为第 43 周位于 10 月 21 日至 27 日之间，我想在 '2019-11-01' 和 '2019-12-03' 之间得到结果。

预期的输出应该像屏幕截图一样。因为从日期“2019-11-01”开始位于 10 月 28 日至 11 月 3 日（第 44 周）之间。所以记录应该从 44 周数开始。

任何想法如何获得正确的周数和日期？

标签： mysqlsql

Answer 1

这是您的查询的一个更易于阅读的版本（使用嵌套子查询，因为 MySQL 5.6 不支持 CTE）并使用DATE_FORMAT格式%x/%v生成week匹配您的预期结果（10 月 28 日是第 44 周的开始）。注意我已经MIN在生成中添加了一个，date以便查询在 MySQL 5.7 中仍然可以使用 SQL 模式 ONLY_FULL_GROUP_BY。

SELECT COUNT(*) AS count,
       CONCAT(DATE_FORMAT(MIN(startofweek), '%M %d'),
              ' - ',
              DATE_FORMAT(MIN(startofweek) + INTERVAL 6 DAY, '%M %d')) AS date,
       week
FROM (SELECT submitted - INTERVAL (dayofweek + 6) % 7 DAY AS startofweek,
             week
      FROM (SELECT nid, 
                   DATE(FROM_UNIXTIME(submitted)) AS submitted, 
                   DATE_FORMAT(FROM_UNIXTIME(submitted), '%w') AS dayofweek,
                   DATE_FORMAT(FROM_UNIXTIME(submitted), '%x/%v') AS week
            FROM webform_submissions
            WHERE nid = 121
              AND DATE(FROM_UNIXTIME(submitted)) BETWEEN '2019-11-01' AND '2019-12-03'
            ) AS dates
      ) AS ws
GROUP BY week

输出（对于我的样本数据）

count   date                        week
3       October 28 - November 03    2019/44
4       November 04 - November 10   2019/45

dbfiddle 上的演示