Oracle中如何判断交易是否处于员工当班时段

问题说明 #

需在Oracle中判断交易是否处于员工当班时段，涉及三列：Transaction_time（交易时间，24小时制）、Shift_Start（班次开始时间，24小时制）、Shift_End（班次结束时间，24小时制）。原有CASE语句仅能识别非跨天班次，无法正确判断跨天班次（如22点至次日6点）的交易归属，导致第三行测试数据（交易时间2点，班次22-6）被错误标记为off shift。

测试数据：

期望输出：

修正后的SQL逻辑 #

核心是区分非跨天班次和跨天班次两种场景，分别设置判断规则：

• 非跨天（Shift_Start <= Shift_End）：交易时间处于班次起止时间区间内则为当班
• 跨天（Shift_Start > Shift_End）：交易时间晚于班次开始时间，或早于班次结束时间则为当班

对应的Oracle SQL代码：

SELECT 
    Transaction_time,
    Shift_Start,
    Shift_End,
    CASE 
        -- 处理非跨天班次
        WHEN Shift_Start <= Shift_End AND Transaction_time BETWEEN Shift_Start AND Shift_End THEN 'on shift'
        -- 处理跨天班次
        WHEN Shift_Start > Shift_End AND (Transaction_time >= Shift_Start OR Transaction_time <= Shift_End) THEN 'on shift'
        -- 其余情况标记为非当班
        ELSE 'off shift'
    END AS shift_flag
FROM your_table_name;

逻辑验证 #

针对测试数据逐一校验：

1. 第一行：班次12-20非跨天，交易时间13在区间内 → on shift
2. 第二行：班次6-14非跨天，交易时间11在区间内 → on shift
3. 第三行：班次22-6跨天，交易时间2≤6 → on shift
4. 第四行：班次1-9非跨天，交易时间17不在区间内 → off shift

完全匹配期望输出结果。

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