带左连接的SQL查询中如何按年份正确筛选数据？

核心问题：WHERE子句逻辑优先级错误 #

筛选结果混入非2022年数据，根源是WHERE子句的逻辑组合存在歧义。SQL中AND优先级高于OR，当前条件会被解析为：

(2022年数据 且 地址匹配%6210 GLENWAY%) 
OR 
(地址匹配%480 WILEY%)

这导致只要地址符合%480 WILEY%，无论年份是否为2022，都会被纳入结果。另外，year(created_datetime)返回数值类型，无需加单引号，写成year(created_datetime) = 2022更规范。

修正后的查询代码 #

方案1：修正WHERE子句逻辑（用括号明确分组） #

将地址筛选的两个条件用括号包裹，确保年份筛选对所有地址条件生效：

select 
l.truck_id,
l.CREATED_DATE,
st.location_name,
st.location_address_1,
st.location_city,
st.location_state_code,
st.location_postal_code,
l.mode_type,
li.weight,
li.hazmat,
li.PRODUCT_DESCRIPTION

from tp.table l

LEFT OUTER JOIN
(Select m.* from
(SELECT truck_ID,hazmat,sum(weight) as 'Weight', product_description,
Row_number() OVER(PARTITION BY truck_ID ORDER BY freight_ID DESC) AS R_NO
FROM [TP].[table2] where status='active'
GROUP BY truck_ID,HAZMAT, PRODUCT_DESCRIPTION,FREIGHT_ID)
m where R_NO=1)li
ON L.truck_ID=li.truck_id

LEFT OUTER JOIN
(Select n.* from
(SELECT truck_ID, location_name, location_address_1, location_city, location_state_code,location_postal_code, 
Row_number() OVER(PARTITION BY truck_ID ORDER BY stop_ID DESC) AS R_NO
FROM [TP].[table3] where status='active' and STOP_SEQUENCE_NUMBER = '1') n where R_NO=1) st
ON L.truck_ID=st.truck_id

where year(created_datetime) = 2022
and (LOCATION_address_1 LIKE '%6210 GLENWAY%'
     OR LOCATION_address_1 LIKE '%480 WILEY%')

方案2：提前筛选主表数据（更高效） #

既然确认要筛选tp.table的2022年数据，直接在主表的FROM子句中提前过滤，减少后续连接的数据量：

select 
l.truck_id,
l.CREATED_DATE,
st.location_name,
st.location_address_1,
st.location_city,
st.location_state_code,
st.location_postal_code,
l.mode_type,
li.weight,
li.hazmat,
li.PRODUCT_DESCRIPTION

-- 提前筛选2022年的主表数据
from (select * from tp.table where year(created_datetime) = 2022) l

LEFT OUTER JOIN
(Select m.* from
(SELECT truck_ID,hazmat,sum(weight) as 'Weight', product_description,
Row_number() OVER(PARTITION BY truck_ID ORDER BY freight_ID DESC) AS R_NO
FROM [TP].[table2] where status='active'
GROUP BY truck_ID,HAZMAT, PRODUCT_DESCRIPTION,FREIGHT_ID)
m where R_NO=1)li
ON L.truck_ID=li.truck_id

LEFT OUTER JOIN
(Select n.* from
(SELECT truck_ID, location_name, location_address_1, location_city, location_state_code,location_postal_code, 
Row_number() OVER(PARTITION BY truck_ID ORDER BY stop_ID DESC) AS R_NO
FROM [TP].[table3] where status='active' and STOP_SEQUENCE_NUMBER = '1') n where R_NO=1) st
ON L.truck_ID=st.truck_id

where (LOCATION_address_1 LIKE '%6210 GLENWAY%'
     OR LOCATION_address_1 LIKE '%480 WILEY%')

额外注意 #

• 若你之前尝试把年份条件移到from tp.table l后（写成from tp.table l where year(created_datetime)=2022），这种写法本身有效，但如果未修正WHERE子句的逻辑错误，依然会出现非2022年数据。
• 确认created_datetime（WHERE子句用的字段）和CREATED_DATE（SELECT中的字段）是你实际要筛选的目标字段。

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