1、创建一个表用于存储假期和调整的上班时间:holiday_of_china。
字段设计:日期(BDATE)、是否假期(HFLAG)、假期名称(NAME)
维护假期信息表,请求接口、手动维护都可以。主要是需标识出一年内的假期以及加班日,数据量一般就30至40条。hflag为true代表假期,false代表加班日。具体数据如下:
![](https://i-blog.csdnimg.cn/blog_migrate/937fa46c2c4f7c4c3b9672cc69c1b480.png)
2、详细代码:
create PROCEDURE preworkdays(
in myear nvarchar(20)
)
begin
SELECT DISTINCT
`part_all`.`bdate` AS `bdate`
FROM
(
SELECT
cast( `part_a`.`bdate` AS CHAR ( 10 ) charset utf8mb4 ) AS `bdate`
FROM
(
SELECT
`data_days`.`bdate` AS `bdate`
FROM
(
SELECT
concat( myear, '-12-31' ) - INTERVAL `a`.`a` + 10 * `b`.`a` + 100 * `c`.`a` DAY AS `bdate`
FROM
((((
SELECT
0 AS `a`
) UNION ALL
SELECT
1 AS `1` UNION ALL
SELECT
2 AS `2` UNION ALL
SELECT
3 AS `3` UNION ALL
SELECT
4 AS `4` UNION ALL
SELECT
5 AS `5` UNION ALL
SELECT
6 AS `6` UNION ALL
SELECT
7 AS `7` UNION ALL
SELECT
8 AS `8` UNION ALL
SELECT
9 AS `9`
) `a`
JOIN (
SELECT
0 AS `a` UNION ALL
SELECT
1 AS `1` UNION ALL
SELECT
2 AS `2` UNION ALL
SELECT
3 AS `3` UNION ALL
SELECT
4 AS `4` UNION ALL
SELECT
5 AS `5` UNION ALL
SELECT
6 AS `6` UNION ALL
SELECT
7 AS `7` UNION ALL
SELECT
8 AS `8` UNION ALL
SELECT
9 AS `9`
) `b`
)
JOIN (
SELECT
0 AS `a` UNION ALL
SELECT
1 AS `1` UNION ALL
SELECT
2 AS `2` UNION ALL
SELECT
3 AS `3` UNION ALL
SELECT
4 AS `4` UNION ALL
SELECT
5 AS `5` UNION ALL
SELECT
6 AS `6` UNION ALL
SELECT
7 AS `7` UNION ALL
SELECT
8 AS `8` UNION ALL
SELECT
9 AS `9`
) `c`
)) `data_days`
WHERE
`data_days`.`bdate` >= concat( myear, '-01-01' )
AND `data_days`.`bdate` <= concat( myear, '-12-31' )) `part_a`
WHERE
dayofweek( `part_a`.`bdate` ) NOT IN ( 1, 7 )
AND !(
`part_a`.`bdate` IN ( SELECT `HOLIDAY_OF_CHINA`.`BDATE` FROM `HOLIDAY_OF_CHINA` WHERE `HOLIDAY_OF_CHINA`.`BHOLIDAY` = 'True' )) UNION ALL
SELECT
`HOLIDAY_OF_CHINA`.`BDATE` AS `bdate`
FROM
`HOLIDAY_OF_CHINA`
WHERE
`HOLIDAY_OF_CHINA`.`BHOLIDAY` = 'False'
AND `HOLIDAY_OF_CHINA`.`BDATE` >= concat( myear, '-01-01' )
AND `HOLIDAY_OF_CHINA`.`BDATE` <= concat( myear, '-12-31' )) `part_all`
ORDER BY
`part_all`.`bdate` DESC;
end;
3、调用:
call preworkdays('2023');
![](https://i-blog.csdnimg.cn/blog_migrate/0492dac2cadd9f4555a72e72e97afe2c.png)