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

蔚落 2022-11-22 12:45 93阅读 0赞

备注:测试数据库版本为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)