mysql - 获取两个日期之间的星期一mysql

Question

我想用 select 语句获取两个日期之间的星期一，但我只在 sql server 中搜索过。

我已经试过了，但是没用:

SELECT DATE_ADD('2017-01-01', INTERVAL ROW DAY) AS Date 
FROM (
            SELECT @row := @row + 1 AS row 
            FROM (
                        SELECT 0 UNION ALL 
                        SELECT 1 UNION ALL 
                        SELECT 3 UNION ALL 
                        SELECT 4 UNION ALL 
                        SELECT 5 UNION ALL 
                        SELECT 6
            ) t1,
            (
                SELECT 0 UNION ALL 
                SELECT 1 UNION ALL 
                SELECT 3 UNION ALL 
                SELECT 4 UNION ALL 
                SELECT 5 UNION ALL 
                SELECT 6
            ) t2, 
            (
                SELECT @row:=-1
            ) t3 LIMIT 31
        ) b
WHERE DATE_ADD('2017-01-01', INTERVAL ROW DAY) BETWEEN '2017-01-01' AND '2017-12-31'
AND DAYOFWEEK(DATE_ADD('2017-01-01', INTERVAL ROW DAY)) = 2

此查询仅提供一个月的星期一日期。

希望你能帮帮我

问候!

Answer 1

如果你真的只想解决这个SQL，你必须更深入地研究并生成一个日期序列，这是迄今为止解决这个问题最困难的副任务。除此之外，正如您已经猜到的那样，您可以使用 DAYOFWEEK或 WEEKDAY得到你想要的日子。

SELECT  * 
   FROM (
        SELECT  DATE_ADD('2013-01-01', 
            INTERVAL n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) AS DATE 
          FROM  (
              SELECT 0 AS num
              UNION ALL SELECT 1
              UNION ALL SELECT 2
              UNION ALL SELECT 3
              UNION ALL SELECT 4
              UNION ALL SELECT 5
              UNION ALL SELECT 6
              UNION ALL SELECT 7
              UNION ALL SELECT 8
              UNION ALL SELECT 9
         ) AS n1,
         (
              SELECT 0 AS num
              UNION ALL SELECT 1
              UNION ALL SELECT 2
              UNION ALL SELECT 3
              UNION ALL SELECT 4
              UNION ALL SELECT 5
              UNION ALL SELECT 6
              UNION ALL SELECT 7
              UNION ALL SELECT 8
              UNION ALL SELECT 9
        ) AS n2,
        (
              SELECT 0 AS num
              UNION ALL SELECT 1
              UNION ALL SELECT 2
              UNION ALL SELECT 3
              UNION ALL SELECT 4
              UNION ALL SELECT 5
              UNION ALL SELECT 6
              UNION ALL SELECT 7
              UNION ALL SELECT 8
              UNION ALL SELECT 9
        ) AS n3,
        (
              SELECT 0 AS num
              UNION ALL SELECT 1
              UNION ALL SELECT 2
              UNION ALL SELECT 3
              UNION ALL SELECT 4
              UNION ALL SELECT 5
              UNION ALL SELECT 6
              UNION ALL SELECT 7
              UNION ALL SELECT 8
              UNION ALL SELECT 9
        ) AS n4
    ) AS a
WHERE DATE >= '2017-01-01' AND DATE < NOW()
  AND WEEKDAY(DATE) = 0
ORDER BY DATE

其他一些 RDBMS 具有内置函数。

如果您有一个所有日期都已经存在的日历表，那就更容易了。