SQL 找出当前月份的第一个和最后一个星期一

SQL 找出当前月份的第一个和最后一个星期一，例如，你希望找出当前月份的第一个和最后一个星期一。

SQL 找出当前月份的第一个和最后一个星期一问题描述

例如，你希望找出当前月份的第一个和最后一个星期一。

SQL 找出当前月份的第一个和最后一个星期一解决方案

在这里我们选择当前月份和星期一。事实上，下面给出的解决方案适用于任何一个月份和一周七天里的任何一天。一周有七天，一旦我们知道了第 1 个星期一对应的日期，那么加上 7 天就能得到第 2 个星期一，加上 14 天就能得到第 3 个星期一。类似地，如果我们知道当前月份最后一个星期一对应的日期，那么减去 7 天就能得到第 3 个星期一，减去 14 天就能得到第 2 个星期一。
DB2
使用 WITH 递归查询生成当前月份的每一天，并使用 CASE 表达式标记所有的星期一。第一个和最后一个星期一分别是最早的和最晚的、带有标记的日期。

 1    with x (dy,mth,is_monday)
 2      as (
 3  select dy,month(dy),
 4         case when dayname(dy)='Monday'
 5              then 1 else 0
 6         end
 7    from (
 8  select (current_date-day(current_date) day +1 day) dy
 9    from t1
10          ) tmp1
11   union all
12  select (dy +1 day), mth,
13         case when dayname(dy +1 day)='Monday'
14               then 1 else 0
15         end
16    from x
17   where month(dy +1 day) = mth
18  )
19  select min(dy) first_monday, max(dy) last_monday
20    from x
21   where is_monday = 1

Oracle
使用 NEXT_DAY 和 LAST_DAY 函数，辅以少许日期计算的技巧，以找出当前月份的第一个和最后一个星期一。

select next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday,
       next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday
  from dual

PostgreSQL
使用 DATE_TRUNC 函数找出当前月份的第一天。有了当前月份第一天的日期，就能通过简单的数学运算（星期日到星期六分别对应数值 1 和 7）得到当前月份的第一个和最后一个星期一。

 1  select first_monday,
 2         case to_char(first_monday+28,'mm')
 3              when mth then first_monday+28
 4                       else first_monday+21
 5         end as last_monday
 6    from (
 7  select case sign(cast(to_char(dy,'d') as integer)-2)
 8              when  0
 9              then dy
10              when -1
11              then dy+abs(cast(to_char(dy,'d') as integer)-2)
12              when 1
13              then (7-(cast(to_char(dy,'d') as integer)-2))+dy
14         end as first_monday,
15         mth
16    from (
17  select cast(date_trunc('month',current_date) as date) as dy,
18         to_char(current_date,'mm') as mth
19    from t1
20         ) x
21         ) y

MySQL
使用 ADDDATE 函数找出当前月份的第一天。有了当前月份第一天的日期，就能通过简单的数学运算（星期日到星期六分别对应数值 1 和 7）得到当前月份的第一个和最后一个星期一。

 1  select first_monday,
 2         case month(adddate(first_monday,28))
 3              when mth then adddate(first_monday,28)
 4                       else adddate(first_monday,21)
 5         end last_monday
 6    from (
 7  select case sign(dayofweek(dy)-2)
 8              when 0 then dy
 9              when -1 then adddate(dy,abs(dayofweek(dy)-2))
10              when 1 then adddate(dy,(7-(dayofweek(dy)-2)))
11         end first_monday,
12         mth
13    from (
14  select adddate(adddate(current_date,-day(current_date)),1) dy,
15         month(current_date) mth
16    from t1
17         ) x
18         ) y

SQL Server
使用 WITH 递归查询生成当前月份的每一天，并使用 CASE 表达式标记所有的星期一。第一个和最后一个星期一分别是最早的和最晚的、带有标记的日期。

 1    with x (dy,mth,is_monday)
 2      as (
 3  select dy,mth,
 4         case when datepart(dw,dy) = 2
 5              then 1 else 0
 6         end
 7    from (
 8  select dateadd(day,1,dateadd(day,-day(getdate()),getdate())) dy,
 9         month(getdate()) mth
10    from t1
11         ) tmp1
12   union all
13  select dateadd(day,1,dy),
14         mth,
15         case when datepart(dw,dateadd(day,1,dy)) = 2
16              then 1 else 0
17         end
18    from x
19   where month(dateadd(day,1,dy)) = mth
20  )
21  select min(dy) first_monday,
22         max(dy) last_monday
23    from x
24   where is_monday = 1

SQL 找出当前月份的第一个和最后一个星期一扩展知识

DB2 和 SQL Server
DB2 和 SQL Server 的解决方案使用了不同的函数，但其做法并无二致。如果我们仔细审视这两个解决方案的话，就会发现它们的唯一差别在于日期的加法运算。下面的讨论将涵盖这两种数据库，但是会借用 DB2 解决方案的代码来演示中间步骤的结果。

如果你没有办法找到支持 WITH 递归查询语法的 SQL Server 或 DB2 版本，不妨采用 PostgreSQL 解决方案的做法。

为了找出当前月份的第一个和最后一个星期一，第一步是找到当前月份的第一天。递归视图 X 里的内嵌视图 TMP1 可以找出当前月份的第一天，它的做法是先找出当前日期，并且要特别地计算出该日期是当前月份的第几天。计算出当前日期是当前月份的第几天，就知道到该日期为止这个月已经过去了多少天（例如，4 月 10 日是 4 月份的第 10 天）。从当前日期里减去该值，就退回到了上个月的最后一天（例如，4 月 10 日减去 10 天，结果就是 3 月份的最后一天）。做过上述减法运算之后，只要再加上 1 天就能得到当前月份的第一天了。

select (current_date-day(current_date) day +1 day) dy
  from t1
　
DY
-----------
01-JUN-2005

下一步要找出当前日期对应的月份，调用 MONTH 函数，并使用简单的 CASE 表达式来确认这个月的第一天是不是星期一。

select dy, month(dy) mth,
        case when dayname(dy)='Monday'
             then 1 else 0
        end is_monday
  from (
select (current_date-day(current_date) day +1 day) dy
  from t1
       ) tmp1
　
DY MTH IS_MONDAY
----------- --- ----------
01-JUN-2005 6 0

接着，借助 WITH 子句的递归特性在当前月份第一天的基础上不断地加上 1 天，直到当前月份最后一天。同时，也可以使用 CASE 表达式来确认每一个日期是不是星期一（星期一将被标记为“1”）。递归视图 X 的部分查询结果如下所示。

with x (dy,mth,is_monday)
     as (
 select dy,month(dy) mth,
        case when dayname(dy)='Monday'
             then 1 else 0
        end is_monday
   from (
 select (current_date-day(current_date) day +1 day) dy
   from t1
        ) tmp1
  union all
 select (dy +1 day), mth,
        case when dayname(dy +1 day)='Monday'
             then 1 else 0
        end
   from x
  where month(dy +1 day) = mth
 )
select *
  from x
　
DY          MTH  IS_MONDAY
----------- --- ----------
01-JUN-2005  6           0
02-JUN-2005  6           0
03-JUN-2005  6           0
04-JUN-2005  6           0
05-JUN-2005  6           0
06-JUN-2005  6           1
07-JUN-2005  6           0
08-JUN-2005  6           0
...

只有星期一对应的 IS_MONDAY 是 1，因而最后一步是针对 IS_MONDAY 等于 1 的行调用聚合函数 MIN 和 MAX，以找出当前月份的第一个和最后一个星期一。
Oracle
有了 NEXT_DAY 函数，本问题就很容易解决了。为了找出当前月份的第一个星期一，先得找到前一个月的最后一天，这需要借助一些日期计算，包括 TRUNC 函数。

select trunc(sysdate,'mm')-1 dy
  from dual
　
DY
-----------
31-MAY-2005

然后，调用 NEXT_DAY 函数计算出紧随前个月最后一天出现的第一个星期一（也就是当前月份的第一个星期一）。

select next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday
  from dual
　
FIRST_MONDAY
------------
06-JUN-2005

为了找出当前月份的最后一个星期一，先要调用 TRUNC 函数计算出当前月份第一天。

select trunc(sysdate,'mm') dy
  from dual
　
DY
-----------
01-JUN-2005

下一步是找出这个月的最后一周（最后 7 天），调用 LAST_DAY 函数找到这个月的最后一天，然后减去 7 天。

select last_day(trunc(sysdate,'mm'))-7 dy
  from dual
　
DY
-----------
23-JUN-2005

我们之所以要从当前月份的最后一天向前倒退 7 天，是为了保证这 7 天里至少剩下一个星期一。最后，调用 NEXT_DAY 函数找到下一个（当前月份最后一个）星期一。

select next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday
  from dual
　
LAST_MONDAY
-----------
27-JUN-2005

PostgreSQL 和 MySQL
PostgreSQL 和 MySQL 解决方案的思路也很类似，差别在于用到的内置函数不同。虽然代码有点长，这两个解决方案的查询语句其实非常简单。而且，在计算当前月份第一个和最后一个星期一的过程中，并未增加多少额外的复杂度。
首先找出当前月份的第一天，紧接着要找出当前月份的第一个星期一。由于没有内置函数可以找到下一个星期一，需要做一些日期运算。（这两个解决方案中任何一个的）第 7 行开始的 CASE 表达式评估当前月份的第一天是不是星期一。（PostgresSQL 的）TO_CHAR 函数在指定了 D 或者 d 格式的情况下会返回 1 到 7，分别表示星期日到星期六，（MySQL 的）DAYOFWEEK 函数亦然。其中，星期一对应的值始终是 2。CASE 表达式要评估的对象是 SIGN 函数的返回值，当前月份的第一天对应的数值（不论它是星期几）减去星期一对应的数值 2，该减法运算的结果传递给 SIGN 函数。如果结果是 0，那么当前月份的第一天就是星期一，并且也是当前月份的第一个星期一。如果结果是 -1，那么当前月份的第一天就是星期日，只要在当前月份的第一天的基础上再加上 2 和 1（分别代表星期一和星期日）之间相差的天数就能得到当前月份的第一个星期一了。

　如果觉得理解起来有点困难，不妨暂时抛开某天是星期几这种想法，只关注数字运算。例如，如果今天是星期二，而我们想知道下一个星期五的日期。可以调用指定了 d 格式参数的 TO_CHAR 函数，也可以调用 DAYOFWEEK 函数，星期五对应数字 6，星期二则对应 3。从 3 数到 6，直接做减法即可（6–3 = 3），然后加上两者之中较小的那个数字（(6–3) + 3 = 6）。因此，先别管具体的日期是什么，如果起始日期对应的数值小于目标日期，那么在起始日期的基础上加上两个日期的差值就能得到目标日期对应的日期了。

如果结果是 1，那么当前月份的第一天就介于星期二和星期六之间（包含起止日期）。如果当前月份第一天对应的数值大于 2（星期一），先算出当前月份第一天和星期一对应的数字（2）之间的差值，用 7 减去该差值，再将上述计算结果加到当前月份的第一天上。这样，就得到了当前月份第一个星期一。

　再次提醒，如果你觉得理解起来有点困难，不妨暂时抛开某天是星期几这种想法，只关注数字运算。例如，假定现在是星期五，我们想找出下个星期二对应的日期。星期二（对应数字 3）比星期五（对应数字 6）小。从 6 数到 3，先计算出两个数字之间差值，并用 7 减去该差值（7-(|3-6|) = 4），然后在起始日星期五的基础上加上这个结果（4）。（“|3-6|”里的竖线表示生成差值对应的绝对值。）这里不能在 6 的基础上再加上 4（这将得到 10），我们需要在星期五的基础上加上 4 天，这样才能得到下一个星期二。

上述 CASE 表达式的思路是，为 PostgreSQL 和 MySQL 实现类似 Oracle 的 NEXT_DAY 函数的功能。如果我们不以当前月份的第一天为起点，DY 的值就变成了 CURRENT_DATE 函数的返回值，而 CASE 表达式的计算结果也就变成了从当前日期开始算起的下一个星期一（如果当前日期是星期一，那么返回值会是其自身）。
现在知道了当前月份的第一个星期一，加上 21 天或者 28 天就能得到当前月份的最后一个星期一。第 2 行到第 5 行之间的 CASE 表达式决定应该加上 21 天还是 28 天，这要视加上 28 天之后会不会落入下个月而定。该 CASE 表达式通过下述步骤实现该计算。
(1) 为 FIRST_MONDAY 加上 28 天。
(2) 调用 PostgreSQL 的 TO_CHAR 函数或者 MySQL 的 MONTH 函数，CASE 表达式从上述 FIRST_MONDAY + 28 的计算结果里得到与之对应的月份
(3) 第 2 步的计算结果会与内嵌视图返回的 MTH 值相比较。MTH 的值是 CURRENT_DATE 对应的月份。如果两个月份相同，那么这个月就大到需要再加上 28 天，因而 CASE 表达式会返回 FIRST_MONDAY + 28。如果两个月份不同，那么加上 28 天就会超出当前月份的范围，因而 CASE 表达式会返回 FIRST_MONDAY + 21。显而易见，要么加上 28 天，要么加上 21 天，我们只需要考虑这两种状况。