生活中常常遇到“几个工作日内”给予答复。那么,我们如何通过MySQL计算最晚答复时间呢?
工作日,不只是有周一到周五,比如:2022-01-03 星期一,元旦放假;2022-10-08|09 虽然是周六周日,但也是需要补调休的算做工作日。
那么,我们要使用代码查出第N个工作日,就首先需要有一个“节假日”数据表。2022年最新的节假日SQL导入文件我已经准备好了,关注公众号,回复“22”即可获得。表格如下:
我使用了我定义function的方式完成。内容如下:
CREATE FUNCTION `get_china_workday`
(start_dt DATE, days INT) RETURNS DATE
DETERMINISTIC
BEGIN
DECLARE res_date DATE DEFAULT start_dt;
DECLARE d_days INT DEFAULT 0;
DECLARE work_flag INT;
WHILE d_days < days DO
SET work_flag = -1;
SET res_date = DATE_ADD(res_date,INTERVAL 1 DAY);
SELECT is_workday INTO work_flag FROM `holiday` WHERE dt = res_date;
IF work_flag = -1 AND WEEKDAY(res_date) < 5 THEN
SET d_days = d_days + 1;
ELSEIF work_flag = 1 THEN
SET d_days = d_days + 1;
END IF;
END WHILE;
RETURN res_date;
END$$
函数名:get_china_workday(start_date, days)
入参: start_date 开始日期
days 第几天
实现思路:
从当前时间开始,调用days次DATE_ADD(res_date,INTERVAL 1 DAY)
每增加一天,查一次holiday表,查询结果一共有三种:
-
查不到:代表是平常日,再确认是不是周一到周五;是则计数。
-
能查到,且is_workday = 0,则说明是假期,不必计数。
-
能查到,且is_workday = 1,则说明虽然是周末,但要补假期调休则应计数。
直到工作日够数,返回最后日期。
测试结果:
遇到的问题:
1. 参数不要和字段名重名。最开始start_dt变量名写的是dt,这与holiday表的日期字段重名了,结果在下面这条数据里,怎么也得不到正确的结果。
WHERE dt = res_date;
2. 函数WEEKDAY(date),week(date),DAYOFWEEK(date)等相似的函数需要回顾
3. MySQL 8.0.17之后不再推荐使用FOUND_ROWS()来查看是否没有数据行。推荐使用文中方式。