SQL 在 WHERE IN 子句中使用 CASE 语句

Question

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

 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)

此代码给出了错误:',' 附近的语法不正确。

Answer 1

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

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)

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

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) 的所有记录