如何在CASE查询中结合IN实现动态数据筛选？

一、直接用逻辑条件替代CASE表达式 #

你之前的错误是因为CASE表达式只能返回单一值，无法返回集合，所以不能直接在IN里嵌套返回多值的子查询。可以直接用逻辑运算符组合条件：

DECLARE @MyValue CHAR(20)
SET @MyValue = '<All>'

SELECT
    RefNo, Name 
FROM
    table1
WHERE 
    (
        (@MyValue IS NULL OR @MyValue = '<All>') 
        AND RefNo IN (SELECT DISTINCT RefNo FROM table2)
    )
    OR
    (@MyValue IS NOT NULL AND @MyValue != '<All>' AND RefNo = @MyValue)

这个写法的逻辑是：

• 当@MyValue为NULL或''时，匹配table1中RefNo存在于table2的记录
• 其他情况，匹配table1中RefNo等于@MyValue的记录

二、用IF语句实现 #

如果偏好分支逻辑，也可以用IF ELSE分开写查询：

DECLARE @MyValue CHAR(20)
SET @MyValue = '<All>'

IF @MyValue IS NULL OR @MyValue = '<All>'
BEGIN
    SELECT
        RefNo, Name 
    FROM
        table1
    WHERE 
        RefNo IN (SELECT DISTINCT RefNo FROM table2)
END
ELSE
BEGIN
    SELECT
        RefNo, Name 
    FROM
        table1
    WHERE 
        RefNo = @MyValue
END

错误原因说明 #

你之前尝试的写法之所以报错，是因为CASE表达式作为标量表达式只能返回单个值，而SELECT DISTINCT G.RefNo FROM table2会返回多个值，无法被CASE表达式承载，进而导致SQL引擎抛出"子查询返回多个值"的错误。

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