如何结合IN、CASE与WHERE子句检索特定数据？附尝试代码

嘿，我看你尝试用IN和CASE组合筛选数据，思路没问题，但写法上有个小误区——IN后面没法直接接CASE返回的多值集合，CASE默认只能返回单个值，所以你的语句才会不符合预期。

先拆解下你的需求：看起来是当用户选择东北地区（对应state_id=37）时，要返回state_id属于(17,1,16)的记录；否则只返回state_id=37的记录？如果是这样，给你两种靠谱的写法：

写法一：用逻辑表达式替代CASE（兼容性拉满） #

这种写法适配几乎所有数据库，逻辑直白清晰：

SELECT st.STATE, l.*
FROM Pulses_tbl_Transition_log l
INNER JOIN Pulses_mst_state st ON st.id = l.state_id
WHERE 
  -- 如果当前记录属于东北地区（state_id=37），就匹配下属州的ID；否则匹配37本身
  (state_id = 37 AND l.state_id IN (17, 1, 16))
  OR
  (state_id != 37 AND l.state_id = 37)

如果你的需求是根据用户选择的ID切换筛选范围（比如用户选37就返回下属州，选其他ID就返回对应单条记录），可以调整成带参数的版本：

-- 假设@user_selected_id是用户选择的目标ID
SELECT st.STATE, l.*
FROM Pulses_tbl_Transition_log l
INNER JOIN Pulses_mst_state st ON st.id = l.state_id
WHERE 
  (
    @user_selected_id = 37 
    AND l.state_id IN (17, 1, 16)
  )
  OR
  (
    @user_selected_id != 37 
    AND l.state_id = @user_selected_id
  )

写法二：用CASE返回集合（部分数据库支持） #

如果你的数据库支持数组/多值返回（比如PostgreSQL、SQL Server），可以用CASE生成目标集合，再配合ANY实现匹配：

-- PostgreSQL示例
SELECT st.STATE, l.*
FROM Pulses_tbl_Transition_log l
INNER JOIN Pulses_mst_state st ON st.id = l.state_id
WHERE 
  l.state_id = ANY(
    CASE 
      WHEN @user_selected_id = 37 THEN ARRAY[17, 1, 16]
      ELSE ARRAY[@user_selected_id]
    END
  )

为啥你的原始写法不行？ #

你写的WHERE state_id IN CASE WHEN state_id = 37 THEN (17 ,1 ,16 ) ELSE '37' END，这里的CASE会把(17,1,16)当成一个整体字符串值，而不是三个独立的ID，数据库根本没法识别成IN的取值列表，自然得不到想要的结果。

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