如何使用CASE WHEN实现location_id动态匹配的SQL查询逻辑？

原代码核心问题 #

1. CASE WHEN用法错误：CASE表达式需返回可用于判断的单一值或布尔结果，原写法在THEN后直接拼接IN子句，不符合SQL语法逻辑，导致无法正常执行。
2. 冗余子查询：子查询完全重复主查询的过滤条件，既增加代码冗余，又会让数据库重复执行相同过滤逻辑，降低查询性能。

优化后的SQL代码 #

SELECT * 
FROM equipment e 
WHERE e.equipment_type = 4
  AND e.start_date <= '2022-01-31'
  AND (e.end_date IS NULL OR e.end_date > '2022-01-01')
  AND e.state = 'TX'
  AND (
    {location_id} = 0 
    OR e.location_id IN {location_id}
  )

逻辑说明 #

• 当用户未输入城市，参数{location_id}为0时，{location_id} = 0条件成立，整个AND括号内的逻辑为真，直接返回符合前面所有过滤条件的德州（TX）所有设备。
• 当用户输入具体城市，参数{location_id}为对应location_id（单个或多个值，如(123)或(123,456)）时，{location_id} = 0不成立，会触发e.location_id IN {location_id}条件，仅查询指定城市的设备。

额外优化建议 #

• 确保state、equipment_type、location_id字段建立联合索引，可大幅提升查询效率，尤其是当equipment表数据量较大时。
• 若系统支持参数为空而非默认0，可调整逻辑为({location_id} IS NULL OR e.location_id IN {location_id})，更符合常规参数传递逻辑。

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