mysql 当前时间加3个工作日

2023-12-13 14:43:14

1. 问题描述:

在日常工作中可能会遇到计算工作日的情况

2. 解决过程

(1) 首先制作一个假日表?holiday_config

CREATE TABLE `holiday_config` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `holiday` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

其中id 为主键自增,holiday 为假期 格式为 '%Y-%m-%d'?

工作日:即不在?holiday_config 表中的数据

a. 增加的三天是否为假日,可能出现的情况有一下四种

????????情况1 :??如果无假日,则直接在原数据的基础上加三天即可,使用date_add(now(),interval 3 DAY);

????????情况2:? ?增加的三天存在假日且小于3 那么直接用??date_add(now(),interval 3+(小于3的数) DAY); ??

????????情况3:??增加的三天存在假日且等于3 那么直接用??date_add(now(),interval 3+3 DAY); ??

????????情况4:??增加的三天存在假日且大于3 那么直接用??date_add(now(),interval 3+(大于3的数) DAY);

b. 如何获取范围内的假日数

假日数小于3 的情况

(SELECT COUNT(1)
? ? ? ? ?FROM holiday_config
? ? ? ? ?WHERE holiday BETWEEN now() AND DATE_ADD(now(), INTERVAL 3 DAY)
?)

大于3 的情况可能有多种,目前只考虑连续假日小于等于10 的情况(具体看下方脚本)

(2) 最终结果

SELECT
    DATE_ADD(
        now(),
        INTERVAL 3 +
        case when   (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 3 DAY)
         )<3
        then (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 3 DAY)
         )
         when (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 4 DAY)
         )<4
         then (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 4 DAY)
         )
         when (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now() AND DATE_ADD(now(), INTERVAL 5 DAY)
         )<5
         then (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 5 DAY)
         )
         when (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 6 DAY)
         )<6
         then (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 6 DAY)
         )
         when (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 7 DAY)
         )<7
         then (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 7 DAY)
         )
         when (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 8 DAY)
         )<8
         then (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 8 DAY)
         )
         when (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 9 DAY)
         )<9
         then (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 9 DAY)
         )
         when (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 10 DAY)
         )<10
         then (SELECT COUNT(1)
         FROM holiday_config
         WHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 10 DAY)
         )
            end
        DAY
    ) AS target_date;

3. 备注

目前只考虑连续假日小于等于10 的情况,如有问题请联系。

文章来源:https://blog.csdn.net/weixin_42550387/article/details/134969713
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。