SQL查询优化：过滤S02和S03时间均在指定时段内的数据

原始数据表 #

需求 #

仅查询S02和S03步骤的cr_time均处于8:30 AM至4:30 PM之间的数据，结果不能包含null值的行。

当前问题SQL #

以下语句会返回含null的行，不符合需求：

select 
    cid,
    min(case when step = 'S02' then cr_time end) S02_time,
    min(case when step = 'S03' then cr_time end) S03_time
from t where
    (CAST (cr_time as TIME) >= '8:30:00 AM' and CAST (cr_time as TIME) <= '4:30:00 PM')
group by cid;

期望结果 #

修正后的SQL方案 #

方案一：使用HAVING子句直接过滤 #

select 
    cid,
    min(case when step = 'S02' then cr_time end) S02_cr_time,
    min(case when step = 'S03' then cr_time end) S03_cr_time
from t
where step in ('S02', 'S03')
group by cid
having 
    -- 验证S02时间符合范围
    min(case when step = 'S02' then CAST(cr_time as TIME) end) >= '08:30:00'
    and min(case when step = 'S02' then CAST(cr_time as TIME) end) <= '16:30:00'
    -- 验证S03时间符合范围
    and min(case when step = 'S03' then CAST(cr_time as TIME) end) >= '08:30:00'
    and min(case when step = 'S03' then CAST(cr_time as TIME) end) <= '16:30:00';

方案二：用CTE预筛选再聚合（更简洁易读） #

with valid_steps as (
    -- 先筛选出S02/S03且时间符合要求的记录
    select cid, step, cr_time
    from t
    where step in ('S02', 'S03')
      and CAST(cr_time as TIME) between '08:30:00' and '16:30:00'
)
select 
    cid,
    max(case when step = 'S02' then cr_time end) S02_cr_time,
    max(case when step = 'S03' then cr_time end) S03_cr_time
from valid_steps
group by cid
-- 确保每个cid同时有S02和S03的有效记录
having count(distinct step) = 2;

说明 #

• 方案二先通过CTE排除掉无关的S04步骤，以及时间不符合的S02/S03记录，减少后续聚合的数据量。
• having count(distinct step) = 2确保每个分组的cid同时拥有S02和S03两条有效记录，避免出现null值列。
• 时间格式用24小时制的'08:30:00'和'16:30:00'更清晰，避免AM/PM的歧义。

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