MySQL 计算本月的第一个和最后一个周一

原创只是甲 2020-11-04

948

备注:测试数据库版本为MySQL 8.0

一.需求:

计算当前月的第一个星期一及最后一个星期一的日期。

二.解决方案

这里选用了Monday和当前月，也可以将该解决方案应用于其他日子和月份。
由于每个相同的周内日期的间隔都是7天，所以知道第一个后，加7就能知道第二个，加14天就能知道第三个。
同样，如果制动啊某个月的最后一个指定的周期内日期，则减7就能得到第三个，再减7就能得到第二个。

使用函数adddate,找到当月的第一天。
得到当前月的第一天之后，可以使用简单的算法和表示星期几的数学值(星期日-星期六分别对应1-7)，
以获得当前月的第一个和最后一个星期一:

select first_monday,
       case month(adddate(first_monday,28))
            when mth then adddate(first_monday,28)
                     else adddate(first_monday,21)
       end last_monday
  from (
select case sign(dayofweek(dy) -2)
            when 0 then dy
            when -1 then adddate(dy,abs(dayofweek(dy) -2))
            when 1  then adddate(dy,(7-(dayofweek(dy) -2)))
       end first_monday,
       mth
   from (
select adddate(adddate(current_date,-day(current_date)),1) dy,
       month(current_date) mth
       ) x
       ) y
复制

测试记录

mysql> select first_monday,
    ->        case month(adddate(first_monday,28))
    ->             when mth then adddate(first_monday,28)
    ->                      else adddate(first_monday,21)
    ->        end last_monday
    ->   from (
    -> select case sign(dayofweek(dy) -2)
    ->             when 0 then dy
    ->             when -1 then adddate(dy,abs(dayofweek(dy) -2))
    ->             when 1  then adddate(dy,(7-(dayofweek(dy) -2)))
    ->        end first_monday,
    ->        mth
    ->    from (
    -> select adddate(adddate(current_date,-day(current_date)),1) dy,
    ->        month(current_date) mth
    ->        ) x
    ->        ) y
    -> ;
+--------------+-------------+
| first_monday | last_monday |
+--------------+-------------+
| 2020-10-05   | 2020-10-26  |
+--------------+-------------+
1 row in set (0.00 sec)
复制

这样看起来很复杂，可以拆解开来
adddate(adddate(current_date,-day(current_date)),1) dy – 当月第一天
month(current_date) – 当月月份
dayofweek(‘2020-10-01’) – 计算当前星期数
(星期日-星期六分别对应1-7)

– 接下来就是一个算法了因为星期一对应的是2，所以来比对大小
sign(dayofweek(dy) -2) 括号里面是整数返回1，是0 返回0，是负数返回 -1
当为0的时候本月第一个星期1就是dy
当为-1的时候代表第一个星期1在本个礼拜，那么此时只要求出距离星期1（2）的差距
adddate(dy,abs(dayofweek(dy) -2))
其实就是加上 2-dayofweek(dy) 即可求到本月的第一个礼拜一

当为1的时候代表本月一号的星期数其实是大于要求的星期数，下一个星期1在下个礼拜
adddate(dy,(7-(dayofweek(dy) -2))) dayofweek(‘2020-10-01’) 是星期四，返回5
7 - (5-2) = 7 - 3 = 4 5+4-7=2 所以这个地方就是要求出这个差距4
这个地方我也想了下，本周是5，其实我要求的是2 2<5 所以求的是2+7,2+7与5的间隔就是要增加的
其实就是 7 +2 - dayofweek

case month(adddate(first_monday,28))
when mth then adddate(first_monday,28)
else adddate(first_monday,21)
end last_monday
这段代码其实就是判断本月有3个还是4个星期1，根据增加了21、28来计算是否本月来计算

用with语句封装下，看起来程序逻辑更为直观

with tmp1 AS
(
select adddate(current_date,-dayofmonth(current_date)+1) dy,month(current_date) mth,dayofweek(current_date) wk
),
tmp2 AS
(
SELECT dy,mth,wk,case sign(wk -2) when 0 then dy
                   when -1 then adddate(dy,abs(wk -2))
                   when 1 then adddate(dy,7+2 - wk)
              end as first_monday
from tmp1
)
SELECT first_monday,
       case  month(adddate(first_monday,28)) when   mth then adddate(first_monday,28)
             else adddate(first_monday,21) 
       end as last_monday
from tmp2
复制

测试记录

mysql> with tmp1 AS
    -> (
    -> select adddate(current_date,-dayofmonth(current_date)+1) dy,month(current_date) mth,dayofweek(current_date) wk
    -> ),
    -> tmp2 AS
    -> (
    -> SELECT dy,mth,wk,case sign(wk -2) when 0 then dy
    ->                    when -1 then adddate(dy,abs(wk -2))
    ->                    when 1 then adddate(dy,7+2 - wk)
    ->               end as first_monday
    -> from tmp1
    -> )
    -> SELECT first_monday,
    ->        case  month(adddate(first_monday,28)) when   mth then adddate(first_monday,28)
    ->              else adddate(first_monday,21)
    ->        end as last_monday
    -> from tmp2
    -> ;
+--------------+-------------+
| first_monday | last_monday |
+--------------+-------------+
| 2020-10-05   | 2020-10-26  |
+--------------+-------------+
1 row in set (0.00 sec)

复制

mysql