1. 問題描述：

在日常工作中可能會(huì)遇到計(jì)算工作日的情況

2. 解決過程

(1) 首先制作一個(gè)假日表?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 表中的數(shù)據(jù)

a. 增加的三天是否為假日，可能出現(xiàn)的情況有一下四種

????????情況1 ：??如果無假日，則直接在原數(shù)據(jù)的基礎(chǔ)上加三天即可，使用date_add(now(),interval 3 DAY);

????????情況2：? ?增加的三天存在假日且小于3 那么直接用??date_add(now(),interval 3+（小于3的數(shù)） DAY); ??

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

????????情況4：??增加的三天存在假日且大于3 那么直接用??date_add(now(),interval 3+（大于3的數(shù)） DAY);

b. 如何獲取范圍內(nèi)的假日數(shù)

假日數(shù)小于3 的情況

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

大于3 的情況可能有多種，目前只考慮連續(xù)假日小于等于10 的情況（具體看下方腳本）

(2) 最終結(jié)果

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. 備注

目前只考慮連續(xù)假日小于等于10 的情況，如有問題請(qǐng)聯(lián)系。文章來源地址http://www.zghlxwxcb.cn/news/detail-758805.html

到了這里，關(guān)于mysql 當(dāng)前時(shí)間加3個(gè)工作日的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！