如何用SQL的CASE语句计算时间区间的重叠天数

需求说明 #

• 统计囚犯服刑期间的有效请假天数，仅计算请假区间与服刑区间重叠的部分
• 服刑区间：ORIG_BED_START 至 ORIG_BED_END（ORIG_BED_END 为 NULL 代表服刑尚未结束，默认以当前日期作为服刑截止）
• 请假区间：LEAVE_START_DAT 至 LEAVE_END_DATE

核心逻辑 #

计算重叠天数的关键是先确定有效重叠区间：

1. 重叠起始时间：取服刑开始和请假开始中较晚的那个（用GREATEST函数）
2. 重叠结束时间：取服刑结束（无则用当前日期）和请假结束中较早的那个（LEAST+COALESCE组合处理NULL）
3. 只有当重叠起始 ≤ 重叠结束时，才计算天数，否则重叠天数为0
4. 天数计算需包含首尾日期，所以用DATEDIFF(结束, 起始) + 1

修正后的SQL语句 #

SELECT
    prisoner_id, -- 替换为你的实际囚犯ID字段
    ORIG_BED_START,
    ORIG_BED_END,
    LEAVE_START_DAT,
    LEAVE_END_DATE,
    -- 计算有效请假天数
    CASE
        WHEN GREATEST(ORIG_BED_START, LEAVE_START_DAT) 
             <= LEAST(COALESCE(ORIG_BED_END, CURRENT_DATE), LEAVE_END_DATE)
        THEN DATEDIFF(
            LEAST(COALESCE(ORIG_BED_END, CURRENT_DATE), LEAVE_END_DATE),
            GREATEST(ORIG_BED_START, LEAVE_START_DAT)
        ) + 1
        ELSE 0
    END AS valid_leave_days
FROM your_table_name; -- 替换为你的实际表名

常见错误修正点 #

如果你的原有CASE语句出错，大概率是以下问题：

• 未处理ORIG_BED_END为NULL的情况：用COALESCE(ORIG_BED_END, CURRENT_DATE)替换直接使用ORIG_BED_END
• 重叠边界判断错误：必须用GREATEST取起始、LEAST取结束，不能直接用原始区间的起止
• 天数计算未包含首尾：忘记加1，导致少算1天

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