“IN” condition at CASE WHEN on WHERE CLAUSE?

问题:

I have an complex situation. I want to write an sql query including "case when" condition on "where clause".

Just like that:

SELECT *
FROM <table>
WHERE
<Column1> in
   CASE <Column2>
      WHEN 1 THEN ('OP', 'CL') 
      WHEN 0 THEN ('RE', 'ST')
END

Column1 must be "in", not "=". Because there is multiple value at condition for Column1. That query returns "Incorrect syntax near ','." error.

Can you give me any suggestion? (Sorry for my bad English.)

EDIT : I think I misunderstood. If Column2 is 1, condition must like that "IN ('OP', 'CL')" else Column1 is 2, condition must like that "IN ('RE', 'ST')".

回答1:

You don't need a CASE expression for that, you can just use OR like this:

SELECT *
FROM <table>
WHERE (Column2 = 1 AND Column1 IN ('OP', 'CL')) OR
    (Column2 = 0 AND Column1 IN ('RE', 'ST'))

回答2:

Select * from table where <Column1> in (Select case <Column2> when 1 then ('OP','CL') when
0 then  ('RE','ST'))