使用mysql查询特定月份和年份的周范围

Question

我想通过传递一个包含年和月的变量来显示周的日期范围，即2016-07像这样在mysql中我怎么能做到这一点:例如:如果我通过2016-07，那么我的输出应该是这样的:

2016-07-03 to 2016-07-10 || 2016-07-11 to 2016-07-17

等等

Answer 1

当你在MySQL中解决这个问题时，有几个部分可以解决这个问题。

一个是决定你的一周是从星期日开始还是星期一开始。这是一个特定于地区的业务规则。在以前属于大英帝国的地方(美国、印度)，通常是星期天。在其他地方，今天是星期一。

所以我们需要这样一个函数:firstDayOfWeek(date)。稍后再详细介绍。

一旦我们有了这个，我们就需要一个方法来得到这个月的最后一天。这很简单;它是一个内置的MySQL函数。LAST_DAY(date)

您已经说过您将使用1941-12这样的字符串指定有问题的月份。我们需要把它变成一个DATE对象。可以这样做:

  STR_TO_DATE(CONCAT(`1941-12`, `-01'), '%Y-%m-%d')

我们需要一个包含0到4整数的虚表。我们称这个表为seq_0_to_4。我们选择这个整数范围是因为每个月的星期天(或星期一)都不超过五个。稍后再详细介绍。

好的，这些是概念上的构建模块。

从一个月的最后一天推导出的一周的第一天是

SET @month := '1941-12';
SET @first_day_of_month := STR_TO_DATE(CONCAT(`1941-12`, `-01'), '%Y-%m-%d')
SET @seed : =FirstDayOfWeek(LAST_DAY(first_day_of_month));

那么你需要4个连续的5个星期开始，最后一个是@seed。

        SELECT @seed - INTERVAL (7*seq.seq) first_day_of_week
          FROM seq_0_to_4

接下来，你需要把它限制在你的月中的天数。

SELECT first_day_of_week, 
       first_day_of_week + INTERVAL 6 DAY last_day_of_week
  FROM (
        SELECT @seed - INTERVAL (7*seq.seq) first_day_of_week
          FROM seq_0_to_4
       ) w
 WHERE first_day_of_week >= @first_day_of_month
 ORDER BY first_day_of_week

这将为您提供一个行表，从该月开始的每个星期都有一行。如果要排除最后一个工作日位于下个月的周，请将WHERE更改为

 WHERE first_day_of_week >= @first_day_of_month
   AND first_day_of_week + INTERVAL 6 DAY <= @seed

最后，要获得您在问题中指定的确切字符串格式，请将该查询包装为:

SELECT GROUP_CONCAT (
             CONCAT(first_day_of_week, ' to ', last_day_of_week) 
             SEPARATOR ' || '
             ORDER BY first_day_of_week)
  FROM (
        SELECT first_day_of_week, 
               first_day_of_week + INTERVAL 6 DAY last_day_of_week
          FROM (
                SELECT @seed - INTERVAL (7*seq.seq) first_day_of_week
                  FROM seq_0_to_4
               ) w
         WHERE first_day_of_week >= @first_day_of_month
       ) x

。

我答应描述FirstDayOfWeek(dt)。

      FROM_DAYS(TO_DAYS(dt) -MOD(TO_DAYS(dt) -1, 7))

这是一个有点神奇的咒语，但它有效。如果你的一周从星期一开始，它是这样的。

       FROM_DAYS(TO_DAYS(dt) -MOD(TO_DAYS(dt) -2, 7))

我答应描述seq_0_to_4。如果你使用的是MySQL的MariaDB分支，它是内置的。如果你使用的是Sun/Oracle分支，你可以这样定义它。

 (SELECT 0 AS seq UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) seq_0_to_4

把它们放在一起:

SET @month := '1941-12';
SET @first_day_of_month := STR_TO_DATE(CONCAT(`1941-12`, `-01'), '%Y-%m-%d');
SET @seed := FROM_DAYS(TO_DAYS(LAST_DAY(first_day_of_month)) 
                       -MOD(TO_DAYS(LAST_DAY(first_day_of_month)) -1, 7));
SELECT GROUP_CONCAT (
             CONCAT(first_day_of_week, ' to ', last_day_of_week) 
             SEPARATOR ' || '
             ORDER BY first_day_of_week)
  FROM (
        SELECT first_day_of_week, 
               first_day_of_week + INTERVAL 6 DAY last_day_of_week
          FROM (
                SELECT @seed - INTERVAL (7*seq.seq) first_day_of_week
                  FROM      (SELECT 0 AS seq UNION SELECT 1 UNION SELECT 2 
                                             UNION SELECT 3 UNION SELECT 4
                            ) seq
               ) w
         WHERE first_day_of_week >= @first_day_of_month
       ) x

用纯mysql方言的SQL来解决你的问题是不合理的复杂(技术术语是一个怪异的毛球)，但这是可能的。

Answer 2

可能太迟了，但我发现这是使用MYSQL 8.0.2中的公共表表达式

WITH RECURSIVE 
Years(y) AS 
        (
        SELECT 2020
        UNION ALL
        SELECT y + 1 FROM Years WHERE y < 2021
        ),
Days (d) AS
        (
        SELECT 1
        UNION ALL
        SELECT d + 1 FROM Days WHERE d < 366
        )
SELECT 
y AS Year,
MONTH(MakeDate(y,d)) AS Month,
WEEK(MakeDate(y,d))+1 -WEEK(TIMESTAMPADD(MONTH,MONTH(MakeDate(y,d))-1,MakeDate(y,1))) AS Week,
Min(MakeDate(y,d)) AS StartDate,
timestampadd(second,-1,timestampadd(day,1,MAx(MakeDate(y,d)))) AS EndDate
FROM Years,Days
WHERE Year(MakeDate(y,d)) <= y
GROUP BY y, MONTH(MakeDate(y,d)),WEEK(MakeDate(y,d))+1 -WEEK(TIMESTAMPADD(MONTH,MONTH(MakeDate(y,d))-1,MakeDate(y,1)))
ORDER BY 1,2,3