Oracle SQL实现基于指定排班规则的班次查询需求

问题描述 #

给定排班规则为5天D班 → 2天R班 → 5天E班 → 2天R班，形成一个14天的循环周期。已知排班起始日期（示例：2023年3月14日），需编写Oracle SQL语句，根据输入日期返回对应的班次。

验证示例 #

• 2023年3月16日 → D
• 2023年3月20日 → R
• 2023年3月30日 → E
• 2023年4月3日 → R
• 2023年4月14日 → D

解决方案 #

以下SQL通过计算输入日期与起始日期的天数差，结合14天循环周期判断班次：

WITH shift_params AS (
    SELECT 
        DATE '2023-03-14' AS start_shift_date, -- 替换为实际排班起始日期
        DATE '2023-03-16' AS target_date       -- 替换为要查询的目标日期
    FROM dual
)
SELECT
    target_date,
    CASE
        -- 第0-4天：5天D班
        WHEN MOD(target_date - start_shift_date, 14) BETWEEN 0 AND 4 THEN 'D'
        -- 第5-6天：2天R班
        WHEN MOD(target_date - start_shift_date, 14) BETWEEN 5 AND 6 THEN 'R'
        -- 第7-11天：5天E班
        WHEN MOD(target_date - start_shift_date, 14) BETWEEN 7 AND 11 THEN 'E'
        -- 第12-13天：2天R班，之后进入下一个循环
        WHEN MOD(target_date - start_shift_date, 14) BETWEEN 12 AND 13 THEN 'R'
    END AS shift_type
FROM shift_params;

逻辑说明 #

1. 参数定义：通过shift_params子句定义排班起始日期和目标查询日期，方便后续修改替换。
2. 天数差计算：Oracle中日期直接相减会返回两个日期之间的整数天数差（target_date - start_shift_date）。
3. 循环周期匹配：使用MOD(天数差, 14)得到目标日期在14天循环周期内的相对位置（0到13）。
4. 班次判断：通过CASE语句根据相对位置匹配对应的班次区间，返回结果。

示例验证 #

以示例中的日期为例：

• 2023-03-16与起始日差2天，MOD(2,14)=2 → 匹配0-4区间，返回D；
• 2023-03-20与起始日差6天，MOD(6,14)=6 → 匹配5-6区间，返回R；
• 2023-04-03与起始日差20天，MOD(20,14)=6 → 匹配5-6区间，返回R；
• 2023-04-14与起始日差31天，MOD(31,14)=3 → 匹配0-4区间，返回D。

若需适配实际排班的起始偏移或周期调整，只需修改CASE语句中的区间范围即可。

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