“IN”可以在CASE语句中使用吗？

Question

我不知道如何表达我的问题，但我会尽力。

我正在尝试在SSRS报告中实现类似于这样的内容。

SELECT COLUMN1, COLUMN2, COLUMN3
FROM TABLE
WHERE COLUMN1 = 
CASE
WHEN @VAR1 IS NOT NULL THEN @VAR1
WHEN @VAR1 IS NULL THEN IN (SELECT COLUMN1 FROM TABLE2)
END

我该如何重写我的case语句以允许这种类型的逻辑？我应该用不同的方式编写它吗？

我知道这个问题可以被解释为一个开放式问题，但只要我测试并且它对我有效，我将标记答案为已回答。

提前致谢。

编辑

Table2的Column1中有NULL值。 我知道'IN'不会产生NULL。 我需要从子查询中提取该列中的所有内容。

- Kajankow42

---

@var1 包含什么内容？这是一个逗号分隔列表的字符串吗？还是单个值？ - Martin Smith

变量包含一个字符串。在我的情况下，如果变量不为空，则用户输入了一个字符串进行搜索和过滤。如果变量为空，则用户不想根据此条件进行过滤。由于他们没有过滤任何内容，我需要子查询的所有值。 - Kajankow42

为了修复您的原始查询，请尝试使用 WHEN @VAR1 IN (SELECT COLUMN1 FROM TABLE2) THEN COLUMN1。但是，当 COLUMN1 为空时，这种方法会出现问题。 - shawnt00

@shawnt00 如果column1为空，而我需要nulls，我该怎么办？ - Kajankow42

Table2中存在哪些类型的数据？Table2是Table1.Column1字段的查找表吗？ - Anup Agrawal

忽略我的关于 null 的说法。只要列值不可为空，你就没问题。 - shawnt00

Answer 1

不行，您不能像想要的那样使用IN。您只需要稍微重新组织一下您的两个可能性就可以了。

SELECT COLUMN1, COLUMN2, COLUMN3
FROM TABLE
WHERE   (@VAR1 IS NOT NULL AND COLUMN1 = @VAR1) OR
        (@VAR1 IS NULL AND COLUMN1 IN (SELECT COLUMN1 FROM TABLE2) )

针对NULL值：

如果要匹配NULL值，您可以尝试以下方法：

SELECT COLUMN1, COLUMN2, COLUMN3
FROM TABLE
WHERE   (@VAR1 IS NOT NULL AND COLUMN1 = @VAR1) OR
        (@VAR1 IS NULL AND 
                (COLUMN1 IN (SELECT COLUMN1 FROM TABLE2) 
                OR COLUMN1 IS NULL) ) )

- Hannover Fist

---

将(@VAR1 IS NOT NULL AND COLUMN1 = @VAR1)简化为(COLUMN1 = @VAR1)。 - Martin Smith

@MartinSmith - 我不确定 - 如果两个都是NULL怎么办？ - Hannover Fist

那么它将会评估为“未知”，而不是“真”。 - Martin Smith

现在我想起来了，你是对的 - 虽然我认为较长的方式更易读。 - Hannover Fist

这个可以工作。但是在table2的column1中有NULL值，我也需要这些NULL值。我知道'IN'不包括NULL值。对于混淆我感到抱歉。我应该在一开始就指定这一点。我会在我的原始帖子中进行编辑。 - Kajankow42

Answer 2

这个怎么样：

SELECT COLUMN1, COLUMN2, COLUMN3
FROM TABLE
WHERE COLUMN1 in
    (SELECT distinct Coalesce(@VAR1, TABLE2.COLUMN1) FROM TABLE2
     UNION SELECT @VAR1 --In case it is possible TABLE2 is empty
    )

应该利用 TABLE 中 Column1 的任何索引。

如果您的列允许为空，那么任何索引可能都无用，但是如果 TABLE2 中存在空值并且您想要返回这些记录，则可以使用以下查询：

SELECT COLUMN1, COLUMN2, COLUMN3
FROM TABLE
WHERE COLUMN1 in
    (SELECT distinct Coalesce(@VAR1, TABLE2.COLUMN1) FROM TABLE2
     UNION SELECT @VAR1
    )
    OR
    (COLUMN1 IS NULL
     AND EXISTS(SELECT distinct TABLE2.COLUMN1 FROM TABLE2
     WHERE TABLE2.COLUMN1 IS NULL 
    )

- Brian Pressler

---

你可以把coalesce也一并去掉。实际上，distinct也是不必要的。 - shawnt00

如果@Var1中有值，那么合并是必要的...我们只想使用那个值。distinct可能只是装饰性的。 - Brian Pressler

每当您使用in子句进行过滤时，也可以使用join进行过滤。哪种方法更快取决于您的数据和实现方式。 - Brian Pressler

Answer 3

你最好创建一个存储过程来处理这个问题，使用动态SQL可以获得更好的性能，使用WHERE @Var is NULL or <Other Expression>将导致执行计划不佳和查询性能变慢。

CREATE PROCEDURE My_Proc 
  @Var1 INT = NULL
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N'SELECT COLUMN1, COLUMN2, COLUMN3
             FROM TABLE
             WHERE 1 = 1'
            + CASE  WHEN @VAR1 IS NOT NULL 
                 THEN N'AND Column1 = @VAR1 '
                    WHEN @VAR1 IS NULL 
                  THEN N'AND Column1  IN (SELECT COLUMN1 FROM TABLE2)'
              END

Execute sp_executesql @Sql 
                     ,'@Var1 INT '
                     ,@Var1 

END

- M.Ali

---

不，这不是优化器出了问题 :) 我认为这是因为错误地使用了 IF ELSE 块。在这种情况下不应该使用它们，我们有动态 SQL 来处理这种情况，所以我们应该在这种情况下使用它。 - M.Ali

如果不是在存储过程中用于流程控制，if else还能用来做什么呢？你已经把我弄糊涂了。 - Hogan

每次编译动态SQL显然是一个次优解。如果您需要频繁运行查询，比如每分钟1000次甚至100次，那么它将会对性能产生巨大的影响。 - Hogan

你真的想不到在T-SQL中IF..ELSE块的其他用途吗？例如验证、变量赋值、数据操作等等。 - M.Ali

不完全是，动态 SQL 有自己的范围，并且将存储参数化执行计划。参数化执行计划仅适用于动态 SQL，因此对于可选参数，动态 SQL 是最佳选择。 - M.Ali

Answer 4

这里有另一种解决问题的方式：

SELECT COLUMN1, COLUMN2, COLUMN3
FROM TABLE
WHERE 1 = 
    CASE
        WHEN @VAR1 IS NOT NULL AND COLUMN1 = @VAR1 THEN 1
        WHEN @VAR1 IS NULL AND COLUMN1 IN (SELECT COLUMN1 FROM TABLE2) THEN 1
        --WHEN @VAR1 = COLUMN3 AND COLUMN1 != COLUMN2 THEN 0 /*example*/
        ELSE
        END

对我来说，这样更容易理解，我可以更轻松地玩弄逻辑。

- Zach Allen

Answer 5

如果您希望输入参数在为null时默认为“全部”（当用户从网站上的列表中进行选择时通常是这种情况），则应执行以下操作：

COLUMN1 = COALESCE(@VAR1,COLUMN1)

或者特别地在SQL Server中

COLUMN1 = ISNULL(@VAR1,COLUMN1)

如果您需要将column1“限制”为另一个表中包含的值，请在查询中包含与该表的内联接。

JOIN TABLE2 ON TABLE.COLUMN1 = TABLE2.COLUMN1

- Hogan