跨双表计算排除周末节假日的阶段停留天数SQL问题排查

错误原因梳理 #

• 多余且逻辑错误的关联：子查询内不需要再次关联base_stage_periods表，你写的关联条件c.date = p.first_time_at_steage AND c.date = p.last_time_stage要求非工作日日期同时等于阶段进入和离开日期，只有进入、离开为同一天的记录才会命中，绝大多数场景下关联不到数据，导致非工作日计数为0，结果偏大。
• 字段歧义：子查询的WHERE条件未限定字段所属表，数据库无法识别first_time_stage、last_time_stage是外层阶段表的字段，会导致计数逻辑完全错误。
• 外层表缺失：你给出的SQL片段没有声明外层查询的数据源，缺少FROM base_stage_periods语句，属于基础语法遗漏。
• 拼写错误：关联条件里的first_time_at_steage是拼写错误，正确字段名应为first_time_at_stage。

修正思路 #

采用关联子查询，直接匹配单条阶段记录对应的时间区间内的非工作日数量，不需要额外在子查询内关联阶段表：

1. 外层查询从阶段表取每条记录的ID、进入/离开阶段时间
2. 子查询统计当前阶段记录的时间区间内，非工作日表的匹配条数
3. 用总间隔天数减去非工作日数量得到实际停留天数

修正后SQL示例（兼容你原有的DATE_DIFF逻辑） #

SELECT 
    p.id,
    p.first_time_at_stage,
    p.last_time_at_stage,
    DATE_DIFF(p.last_time_at_stage, p.first_time_at_stage, DAY) 
    - COALESCE(
        (SELECT COUNT(1)
         FROM base_holidays_and_weekends c
         WHERE c.date BETWEEN p.first_time_at_stage AND p.last_time_at_stage), 
    0) AS time_spent_in_stage
FROM base_stage_periods p

补充说明：

1. 如果你的业务要求停留天数包含离开阶段当天，将DATE_DIFF部分改为DATE_DIFF(p.last_time_at_stage, p.first_time_at_stage, DAY) + 1即可，可匹配你给出的示例计算结果。
2. 如果阶段表每条记录对应唯一的对象阶段流转记录，可移除原SQL里的DISTINCT关键字，降低性能开销。

内容的提问来源于stack exchange，提问作者E.A.