MySQL计算工作日（处理假期及加班）

1、创建一个表用于存储假期和调整的上班时间：holiday_of_china。

字段设计：日期（BDATE）、是否假期(HFLAG)、假期名称(NAME)

维护假期信息表，请求接口、手动维护都可以。主要是需标识出一年内的假期以及加班日，数据量一般就30至40条。hflag为true代表假期，false代表加班日。具体数据如下：

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');