Oracle SQL开发：基于循环班次规则计算目标日期班次

问题背景 #

已知起始日期3/14/2022，目标日期4/2/2022，循环班次规则为5D,2R,5E,2R（即连续5天D班→2天休息→5天E班→2天休息，重复循环），需计算目标日期对应的班次（预期结果为R）。

解决方案思路 #

1. 计算起始日到目标日的天数差，确定目标日在循环周期中的相对位置。
2. 拆解班次规则：总循环周期为5+2+5+2=14天，明确每个班次对应的天数区间。
3. 通过取模运算MOD(天数差, 14)得到目标日在周期内的位置，匹配对应的班次。

实现代码 #

WITH shift_cycle AS (
    -- 拆解循环班次的天数区间与对应班次
    SELECT 'D' AS shift_code, 0 AS start_day, 4 AS end_day FROM DUAL -- 第0-4天：D班（共5天）
    UNION ALL
    SELECT 'R' AS shift_code, 5 AS start_day, 6 AS end_day FROM DUAL -- 第5-6天：休息（共2天）
    UNION ALL
    SELECT 'E' AS shift_code, 7 AS start_day, 11 AS end_day FROM DUAL -- 第7-11天：E班（共5天）
    UNION ALL
    SELECT 'R' AS shift_code, 12 AS start_day, 13 AS end_day FROM DUAL -- 第12-13天：休息（共2天）
),
date_calc AS (
    -- 计算起始日到目标日的天数差
    SELECT 
        TRUNC(TO_DATE('4/2/2022', 'MM/DD/YYYY')) - TRUNC(TO_DATE('3/14/2022', 'MM/DD/YYYY')) AS days_interval
    FROM DUAL
)
SELECT sc.shift_code AS target_shift
FROM shift_cycle sc
JOIN date_calc dc ON MOD(dc.days_interval, 14) BETWEEN sc.start_day AND sc.end_day;

结果验证 #

• 天数差计算：3/14/2022到4/2/2022共19天（31-14+2=19）。
• 周期内位置：19 MOD 14 = 5，对应shift_cycle中start_day=5、end_day=6的区间，班次为R，与预期一致。

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