Oracle 19c 工作日减法实现：是否有官方新增的便捷方案？

截至2024年，Oracle尚未将直接的工作日加减功能纳入基础SQL函数或标准系统包中，但已有比早期自定义函数、复杂CASE计算更简洁的实现方式，以下是几种实用方案：

方案1：递归CTE实现（仅排除周末） #

这种方式逻辑清晰，无需自定义函数，适合仅需排除周六周日的场景：

WITH workdays AS (
    SELECT DATE '2024-05-20' AS dt, 0 AS cnt FROM dual -- 替换为你的起始日期
    UNION ALL
    SELECT dt - 1, cnt + CASE WHEN TO_CHAR(dt - 1, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN') THEN 1 ELSE 0 END
    FROM workdays
    WHERE cnt < 5 -- 替换为要减去的工作日数
)
SELECT MIN(dt) AS target_date
FROM workdays
WHERE cnt = 5; -- 与上面的工作日数保持一致

方案2：递归CTE扩展（支持排除节假日） #

如果需要同时排除法定节假日，只需结合你的节假日表进行判断：

WITH workdays AS (
    SELECT DATE '2024-05-20' AS dt, 0 AS cnt FROM dual -- 起始日期
    UNION ALL
    SELECT dt - 1, cnt + CASE WHEN TO_CHAR(dt - 1, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN') 
                                AND NOT EXISTS (SELECT 1 FROM holidays WHERE holiday_date = dt - 1)
                             THEN 1 ELSE 0 END
    FROM workdays
    WHERE cnt < 5 -- 要减去的工作日数
)
SELECT MIN(dt) AS target_date
FROM workdays
WHERE cnt = 5;

注：假设你已有名为holidays的表，包含holiday_date字段存储节假日日期。

方案3：数学计算式（无递归，仅排除周末） #

如果偏好非递归的写法，可以通过数学估算加调整的方式实现：

WITH initial_calc AS (
    SELECT DATE '2024-05-20' - 5 - -- 起始日期减去目标工作日数
           FLOOR((DATE '2024-05-20' - 5 - DATE '2024-05-20') / 7) * 2 -
           CASE WHEN TO_CHAR(DATE '2024-05-20', 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN 1 ELSE 0 END
           AS temp_date
    FROM dual
),
adjusted_date AS (
    SELECT temp_date - 
           CASE WHEN TO_CHAR(temp_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN 
               CASE TO_CHAR(temp_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') 
                   WHEN 'SAT' THEN 1 
                   WHEN 'SUN' THEN 2 
               END 
           ELSE 0 END
           AS target_date
    FROM initial_calc
)
SELECT target_date FROM adjusted_date;

以上方案均无需创建自定义函数，写法比早期的复杂CASE语句更简洁易读，且可根据需求灵活扩展。

内容的提问来源于stack exchange，提问作者Felipe Vidal