mysql工作日函数

SELECT 
    DATE('2023-10-10') AS date,
    CASE 
        WHEN DAYOFWEEK(date) BETWEEN 1 AND 5 THEN '工作日'
        ELSE '非工作日'
    END AS is_workday;

-- 创建一个包含节假日的表
CREATE TABLE holidays (
    date DATE PRIMARY KEY
);

-- 插入一些节假日数据
INSERT INTO holidays (date) VALUES ('2023-10-01'), ('2023-10-02'), ('2023-12-25');

-- 计算两个日期之间的工作日数量，排除节假日
SELECT 
    COUNT(*) AS workdays
FROM 
    (SELECT @rownum:=@rownum+1 AS rownum, DATE_ADD('2023-10-01', INTERVAL @rownum DAY) AS date
     FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) t1,
          (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) t2,
          (SELECT @rownum:=0) r
     WHERE DATE_ADD('2023-10-01', INTERVAL @rownum DAY) <= '2023-10-31') dates
WHERE 
    DAYOFWEEK(date) BETWEEN 1 AND 5
    AND NOT EXISTS (SELECT 1 FROM holidays WHERE date = dates.date);