SQL Server的CASE WHEN和IN语句结构

Question

遇到了一个WHERE搜索语句的问题，想使用像这样的结构..

WHERE f.foo IN 
   CASE @bar
      WHEN 'BAR' THEN 
         ('FOO','BAR',BAZ')
      WHEN 'BAZ' THEN
         ('FOOBAR','FOOBAZ')
   END

或者

WHERE CASE @bar
      WHEN 'BAR' THEN 
         f.foo IN ('FOO','BAR',BAZ')
      WHEN 'BAZ' THEN
         f.foo IN ('FOOBAR','FOOBAZ')
   END

在这里，@bar是一个定义良好的正确类型的临时变量，并且所有的f都被很好地定义了。

我遇到了一个关于“错误在','处”的错误。

- Jim Ward

---

这并不是丢失了'符号，而是我的打字水平次一些。 - Jim Ward

Answer 1

WHERE (@bar = 'BAR' and f.foo IN ('FOO', 'BAR', 'BAZ')) OR
      (@bar = 'BAZ' and f.foo IN ('FOOBAR', 'FOOBAZ'))

- Adam Robinson

---

出于维护的考虑，我更喜欢这种写法。如果还有其他情况需要@bar = 'BAR'，与本题结构嵌套相比，这种写法会更易读。 - Anthony Potts

我也会选择这个答案而不是下一个，我更倾向于速度，但意识到在维护周期中我不会再看到这段代码。 谢谢大家。 - Jim Ward

Answer 2

SELECT  *
FROM    …
WHERE   @bar = 'BAR'
        AND foo IN ('FOO', 'BAR', 'BAZ')
UNION ALL        
SELECT  *
FROM    …
WHERE   @bar = 'BAZ'
        AND foo IN ('FOOBAR', 'FOOBAZ')

这将是最高效的索引。

SQL Server 将根据 @bar 的值优化其中一个查询，并使用 foo 上的索引来执行剩余的查询。

更新：

表 master 共有 20,000,000 条记录，其中有 2,000,000 条记录的 name 为“t”。

此查询：

DECLARE @s INT
SET @s = 2
SELECT  *
FROM    master
WHERE   (@s = 1 AND name IN ('t')) OR
        (@s = 2 AND name IN ('zz'))

使用索引扫描，且在4秒内未返回任何结果：

  |--Parallelism(Gather Streams)
       |--Index Scan(OBJECT:([test].[dbo].[master].[ix_name_desc]),  WHERE:([@s]=(1) AND [test].[dbo].[master].[name]='t' OR [@s]=(2) AND [test].[dbo].[master].[name]='zz'))

这个查询：

DECLARE @s INT
SET @s = 2
SELECT  *
FROM    master
WHERE   @s = 1 AND name IN ('t')
UNION ALL
SELECT  *
FROM    master
WHERE   @s = 2 AND name IN ('zz')

使用两个单独查询的 连接（CONCATENATION）（其中一个被优化掉），并立即返回结果：

  |--Concatenation
       |--Parallelism(Gather Streams)
       |    |--Filter(WHERE:(STARTUP EXPR([@s]=(1))))
       |         |--Index Seek(OBJECT:([test].[dbo].[master].[ix_name_desc]), SEEK:([test].[dbo].[master].[name]='t') ORDERED FORWARD)
       |--Filter(WHERE:(STARTUP EXPR([@s]=(2))))
            |--Index Seek(OBJECT:([test].[dbo].[master].[ix_name_desc]), SEEK:([test].[dbo].[master].[name]='zz') ORDERED FORWARD)

- Quassnoi

---

+1. 我之前没有考虑过在这种情况下使用 UNION。我需要测试一下与使用 OR 条件相比哪个更高效。谢谢。 - NotMe

谢谢你构建那个测试用例。你刚刚让我确信自己做了一些非常非常错误的事情。 :) - NotMe

Answer 3

作为一个猜测，你这行代码是否缺少一个 ' ：

f.foo IN ('FOO','BAR',BAZ')

应该是这样的

f.foo IN ('FOO','BAR','BAZ')

- Jason Evans

Answer 4

您可以省略查询语句中的“case”部分。例如：

WHERE ((@bar = 'BAR') AND (f.foo IN ('FOO','BAR','BAZ')))
OR ((@bar = 'BAZ') AND (f.foo in ('FOOBAR', 'FOOBAZ')))

- NotMe

Answer 5

Case是一个表达式而不是语句。

- Anthony Potts

Answer 6

我不相信你能做到这样的构建，所以你只能被困在类似的东西中:

where
  (@bar = 'BAR' and (f.foo = 'FOO' or f.foo = 'BAR' or f.foo = 'BAZ')) or
  (@bar = 'BAZ' and (f.foo = 'FOOBAR' or f.foo = 'FOOBAZ'))

或者：

where @bar + '_' + f.foo in
  ('BAR_FOO', 'BAR_BAR', 'BAR_BAZ', 'BAZ_FOOBAR', 'BAZ_FOOBAZ')

- Guffa

Answer 7

CASE语句只允许标量输出。您可能希望以这种方式处理它

WHERE   CASE 
            WHEN @bar = 'BAR' AND @foo = 'FOO' THEN 1 
            WHEN @bar = 'BAR' AND @foo = 'BAR' THEN 1 
            WHEN @bar = 'BAR' AND @foo = 'BAZ' THEN 1 
            WHEN @bar = 'BAZ' AND @foo = 'FOOBAR' THEN 1
            WHEN @bar = 'BAZ' AND @foo = 'FOOBAZ' THEN 1
            ELSE 0
        END  = 1 

- Ender

Answer 8

你可以在这行上进行一些操作：

WHERE (@bar='BAR' AND f.foo IN ('FOO','BAR','BAZ'))
    OR (@bar='BAZ' AND f.foo IN ('FOOBAR','FOOBAZ'))

理解为什么代码片段无法正常工作也很重要（除了引号不匹配和其他语法错误）。CASE语句不是控制流结构。它不会选择一条代码分支并将其插入到SQL中。相反，它评估其内容并返回一个表达式，就像函数调用一样。

- Álvaro González

Answer 9

WHERE CASE @bar
      WHEN 'BAR' THEN 
         f.foo IN ('FOO','BAR','BAZ')
      WHEN 'BAZ' THEN
         f.foo IN ('FOOBAR','FOOBAZ')
   END

你漏掉了一个 ' 在 BAZ 前面

- Polichism