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)

因为 或 P.Status IN (1,3) 还为您提供了 @Status='published' 和 P.Status IN (1,3) 的所有记录

since or P.Status IN (1,3) gives you also all records of @Status='published' and P.Status IN (1,3)

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