SQL Server中能否在CASE语句内使用IN？参数化筛选需求问询

问题背景 #

需要根据@Daily参数筛选特定类型的数据行，当前用两个独立SELECT语句实现，但除WHERE子句外代码大量冗余。尝试用CASE语句结合IN操作符简化但语法无效，且无法为表添加每日标识字段。

原冗余实现代码 #

IF @Daily = 1
SELECT DISTINCT Column001, Column002, Column003, Column004
        FROM            table1
        INNER JOIN      table2 ON table2.Ref = table1.Ref
        INNER JOIN      table3 ON table3.Ref= table2.Ref
        WHERE           table3.Type IN (1, 2, 3, 4)

IF @Daily = 0
SELECT DISTINCT Column001, Column002, Column003, Column004
        FROM            table1
        INNER JOIN      table2 ON table2.Ref = table1.Ref
        INNER JOIN      table3 ON table3.Ref= table2.Ref
        WHERE           table3.Type IN (5, 6, 7, 8)

尝试的无效语法 #

SELECT DISTINCT Column001, Column002, Column003, Column004
        FROM            table1
        INNER JOIN      table2 ON table2.Ref = table1.Ref
        INNER JOIN      table3 ON table3.Ref= table2.Ref
        WHERE           CASE WHEN @Daily = 1 THEN table3.Type IN (1, 2, 3, 4)
                             WHEN @Daily = 0 THEN table3.Type IN (5, 6, 7, 8)
                             END;

可行解决方案 #

你尝试的CASE写法不可行，因为CASE表达式只能返回单个值，无法直接返回逻辑判断结果。以下几种方法可以简化查询，消除冗余代码：

方法1：逻辑运算符组合条件 #

直接在WHERE子句中通过参数值匹配对应Type范围，写法简洁直观：

SELECT DISTINCT Column001, Column002, Column003, Column004
FROM table1
INNER JOIN table2 ON table2.Ref = table1.Ref
INNER JOIN table3 ON table3.Ref = table2.Ref
WHERE 
    (@Daily = 1 AND table3.Type IN (1, 2, 3, 4))
    OR 
    (@Daily = 0 AND table3.Type IN (5, 6, 7, 8));

方法2：IN子句结合条件映射 #

如果参数只有0和1两种取值，也可以用子查询映射参数与Type的对应关系，兼容性更好：

SELECT DISTINCT Column001, Column002, Column003, Column004
FROM table1
INNER JOIN table2 ON table2.Ref = table1.Ref
INNER JOIN table3 ON table3.Ref = table2.Ref
WHERE table3.Type IN (
    SELECT val 
    FROM (
        VALUES 
            (1,1), (2,1), (3,1), (4,1),
            (5,0), (6,0), (7,0), (8,0)
    ) AS t(val, daily_flag)
    WHERE t.daily_flag = @Daily
);

方法3：动态SQL（适合复杂场景） #

如果后续参数逻辑需要扩展，动态SQL是灵活的选择，但要注意防范SQL注入风险：

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'
SELECT DISTINCT Column001, Column002, Column003, Column004
FROM table1
INNER JOIN table2 ON table2.Ref = table1.Ref
INNER JOIN table3 ON table3.Ref = table2.Ref
WHERE table3.Type IN (' + 
    CASE @Daily 
        WHEN 1 THEN '1,2,3,4'
        WHEN 0 THEN '5,6,7,8'
    END + N');';
EXEC sp_executesql @sql;

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