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) 最终结果文章来源:https://www.toymoban.com/news/detail-758805.html
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://www.toymoban.com/news/detail-758805.html
到了这里,关于mysql 当前时间加3个工作日的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!