SQL在WHERE IN子句中使用CASE语句 [英] SQL use CASE statement in WHERE IN clause

问题描述

是否可以在 where in 子句中使用 case ? 像这样:

Is it posible to use case in where in clause? Something like this:

 DECLARE @Status VARCHAR(50);
 SET @Status='published';

 SELECT * FROM Product P    
 WHERE P.Status IN (CASE WHEN @Status='published' THEN (1,3)
                                   WHEN @Status='standby' THEN (2,5,9,6)
                                   WHEN @Status='deleted' THEN (4,5,8,10)
                                   ELSE (1,3)
                                   END)

此代码给出错误:'，'附近的语法不正确.

推荐答案

不，您不能像这样使用case和in.但是你可以做

No you can't use case and in like this. But you can do

SELECT * FROM Product P    
WHERE @Status='published' and P.Status IN (1,3)
or @Status='standby' and P.Status IN  (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)

顺便说一句，您可以将其减少为

BTW you can reduce that to

SELECT * FROM Product P    
WHERE @Status='standby' and P.Status IN (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)

因为or P.Status IN (1,3)还为您提供了@Status='published' and P.Status IN (1,3)

这篇关于SQL在WHERE IN子句中使用CASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！