筛选指定时间戳区间记录：WHERE语句异常求助

需要将查询结果限制为time_stamp_local在13:00:00至14:00:00之间的记录，但当前WHERE语句存在逻辑错误，导致结果仍包含早于13:00的记录，原SQL代码如下：

declare @dateFrom date = '2022-09-01'
declare @dateTo date   = '2022-09-30'
declare @TimeFrom time(0) = '13:00:00'
declare @TimeTo time(0) = '14:00:00'
 
select T.external_id as Stop_ID,PC2.time_stamp_local,isnull(sum([in]),0) as Boarding_Passengers,isnull(sum([out]),0) as Alighting_Passengers,
T.LineName as Line, CAST(PC2.time_stamp_local as time(0)) as Time, CAST(Convert(varchar,@TimeFrom) as time(0)) as FROM_
from
(
select sp.external_id,sp.descr,sp.stop_point_id,L.line_id,L.descr as LineName
from apt..apt_calendar C
       INNER join apt..apt_journey_in_block JIB on C.network_ver_id = JIB.network_ver_id and C.period_id = JIB.period_id
       INNER join apt..apt_journey J on JIB.network_ver_id = J.network_ver_id and jib.journey_id = J.journey_id
       INNER join apt..apt_line L on C.network_ver_id = L.network_ver_id and J.line_id = L.line_id
       INNER join apt..apt_point_in_journey_pattern PIJP ON J.network_ver_id = PIJP.network_ver_id and J.journey_pattern_id = PIJP.journey_pattern_id
       INNER join apt..apt_stop_point SP on C.network_ver_id = SP.network_ver_id and PIJP.stop_point_id = SP.stop_point_id
where cast(c.calendar_day as date) between @dateFrom and @dateTo and j.company_id like '11%'
group by sp.external_id,sp.descr,sp.stop_point_id,L.line_id,L.descr
) T
LEFT JOIN [I4M].[VS].[vs_passenger_count2] PC2 ON cast(PC2.calendar_day as date) between @dateFrom and @dateTo and PC2.stop_point_id = T.stop_point_id and PC2.line_id = T.line_id
left join  i4m.vs.vs_passenger_count2_door PC2D on PC2.id = PC2D.passengar_count_id and PC2.error_flags = 0
WHERE PC2D.[in] != 0 or PC2D.[out] != 0 and CAST(PC2.time_stamp_local as time(0)) > cast(Convert(varchar,@TimeFrom) AS TIME(0)) 
    and CAST(PC2.time_stamp_local as time(0)) < cast(Convert(varchar,@TimeTo) AS TIME(0))
group by T.external_id,PC2.time_stamp_local,T.LineName,T.line_id--,CAST(PC2.time_stamp_local as time(0))
order by T.LineName

问题原因 #

核心是逻辑运算符优先级错误：SQL中AND的优先级高于OR，原WHERE子句会被解析为：

PC2D.[in] != 0 OR (PC2D.[out] != 0 AND 时间条件)

这意味着只要PC2D.[in] != 0，无论时间是否在13:00-14:00之间，这条记录都会被筛选出来，这就是早于13:00的记录出现在结果中的原因。

另外，代码中存在不必要的类型转换：cast(Convert(varchar,@TimeFrom) AS TIME(0))完全多余，直接使用变量@TimeFrom即可。

修正方案 #

1. 给OR的两个条件加上括号，确保时间条件作用于所有符合[in]或[out]不为0的记录
2. 简化时间比较逻辑，直接使用时间变量
3. 注意：原语句中LEFT JOIN PC2D后在WHERE里筛选PC2D.[in] !=0 OR PC2D.[out] !=0，会将LEFT JOIN隐式转为INNER JOIN（因为NULL值会被排除），如果需要保留T中没有匹配乘客数据的站点，需调整为在JOIN时添加条件，或者用ISNULL处理。

修正后的SQL代码：

declare @dateFrom date = '2022-09-01'
declare @dateTo date   = '2022-09-30'
declare @TimeFrom time(0) = '13:00:00'
declare @TimeTo time(0) = '14:00:00'
 
select 
    T.external_id as Stop_ID,
    PC2.time_stamp_local,
    isnull(sum([in]),0) as Boarding_Passengers,
    isnull(sum([out]),0) as Alighting_Passengers,
    T.LineName as Line, 
    CAST(PC2.time_stamp_local as time(0)) as Time, 
    @TimeFrom as FROM_
from
(
select sp.external_id,sp.descr,sp.stop_point_id,L.line_id,L.descr as LineName
from apt..apt_calendar C
       INNER join apt..apt_journey_in_block JIB on C.network_ver_id = JIB.network_ver_id and C.period_id = JIB.period_id
       INNER join apt..apt_journey J on JIB.network_ver_id = J.network_ver_id and jib.journey_id = J.journey_id
       INNER join apt..apt_line L on C.network_ver_id = L.network_ver_id and J.line_id = L.line_id
       INNER join apt..apt_point_in_journey_pattern PIJP ON J.network_ver_id = PIJP.network_ver_id and J.journey_pattern_id = PIJP.journey_pattern_id
       INNER join apt..apt_stop_point SP on C.network_ver_id = SP.network_ver_id and PIJP.stop_point_id = SP.stop_point_id
where cast(c.calendar_day as date) between @dateFrom and @dateTo and j.company_id like '11%'
group by sp.external_id,sp.descr,sp.stop_point_id,L.line_id,L.descr
) T
LEFT JOIN [I4M].[VS].[vs_passenger_count2] PC2 
    ON cast(PC2.calendar_day as date) between @dateFrom and @dateTo 
    and PC2.stop_point_id = T.stop_point_id 
    and PC2.line_id = T.line_id
left join  i4m.vs.vs_passenger_count2_door PC2D 
    on PC2.id = PC2D.passengar_count_id 
    and PC2.error_flags = 0
    -- 如果需要保留无乘客数据的站点，将[in]/[out]条件移到JOIN这里
    -- and (PC2D.[in] != 0 or PC2D.[out] != 0)
WHERE 
    -- 用括号包裹OR条件，确保时间条件作用于所有情况
    (PC2D.[in] != 0 or PC2D.[out] != 0)
    and CAST(PC2.time_stamp_local as time(0)) > @TimeFrom
    and CAST(PC2.time_stamp_local as time(0)) < @TimeTo
group by T.external_id,PC2.time_stamp_local,T.LineName,T.line_id
order by T.LineName

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