Switch case/If在where子句中的应用

Question

我正在尝试根据某个值更改where子句的一部分条件。我查看了与此类似的文章，包括：SQL where子句中的switch/case，但它们并没有完全涉及到我的情况。

我有一个存储过程，它根据其输入执行选择操作。这些输入是可选的（或者按照他们的说法有默认参数）。

其中两个参数是数字。

• 如果只指定第一个数字，则列X必须等于该数字。
• 如果指定第一个和第二个数字，则列X必须>=第一个数字且<=第二个数字。
• 如果仅指定第二个数字，则无效。

这是我尝试的内容（显然不起作用）：

DECLARE @SECOND INT;
DECLARE @FIRST INT;

SET @FIRST = 123456;
SET @SECOND = 67890;

SELECT * FROM BANK_DETAIL 
WHERE -- POSSIBLY SOME OTHER WHERE CLAUSES
    CASE
        WHEN @SECOND IS NULL THEN 
            X = @FIRST
        ELSE 
            X >= @FIRST AND X <= @SECOND 
    END
        -- POSSIBLY SOME MORE WHERE CLAUSES
ORDER BY X

实际上，这似乎需要一个IF/ELSE而不是CASE，但我被指向了CASE.....

哦，这是MS SQL >= 2005。

- Jon H

---

请查看：http://www.sommarskog.se/dyn-search-2005.html - OMG Ponies

Answer 1

尝试基于@SECOND的空值性进行条件判断，而不是使用CASE语句。

SELECT *
FROM BANK_DETAIL
WHERE
-- other conditions go here
AND ((@SECOND IS NULL AND X = @FIRST)
  OR (@SECOND IS NOT NULL AND X >= @FIRST AND X <= @SECOND))

- dmc

---

+1 围绕整个内容加括号，但我认为您不需要 IS NOT NULL。 - JNK

任何与NULL的比较都不会评估为假。它评估为NULL。 - Tom H

如果我对三值逻辑更加熟悉，我可能会消除“IS NOT NULL”条件。但是对我来说，保留它似乎更清晰明了。也许一年后我会有不同的看法... - dmc

Answer 2

SELECT * FROM BANK_DETAIL 
WHERE
    (@SECOND IS NULL AND X = @FIRST) or
    (X >= @FIRST AND X <= @SECOND)

虽然这样做更有效率：

IF @SECOND IS NULL
BEGIN
   SELECT * FROM BANK_DETAIL 
   WHERE
     X = @FIRST
END
ELSE
BEGIN
   SELECT * FROM BANK_DETAIL 
   WHERE
     X >= @FIRST AND X <= @SECOND
END

- Steve Mayne

---

根据缓存的情况，我认为你的第二种方法并不总是更高效（同时还会导致重复代码维护问题）。 - Tom H

@Tom：这取决于计划程序。我不熟悉SQL-Server的内部结构，但是Postgres会以这样的方式准备第一个查询，即进行两个位图索引扫描（导致重新检查条件，并且其中一个可能不需要），并且它将准备下面两个查询，使它们执行正常的索引扫描。 - Denis de Bernardy

在范围内搜索与搜索特定值是不同的。当然，这取决于表中的数据、统计信息和索引。作为一个经验法则，在我的经验中，OR子句通常不太适合用于WHERE子句的顶层。 - Steve Mayne

Answer 3

我会使用布尔运算符：

SELECT * FROM BANK_DETAIL 
WHERE
  ((@SECOND IS NULL AND X = @FIRST) OR 
  (@SECOND IS NOT NULL AND X >= @FIRST AND X <= @SECOND));

- Daniel Hilgarth

Answer 4

你正在构建一个动态搜索条件。通过强制一个语句来涵盖两种情况，你正在削减优化器的选项。生成的计划必须在@seconds为null和不为null的两种情况下都能工作。最好使用两个单独的语句：

IF @SECOND IS NULL THEN
   SELECT * FROM BANK_DETAIL 
   WHERE  X = @FIRST
   ORDER BY X
ELSE
   SELECT * FROM BANK_DETAIL 
   WHERE X >= @FIRST AND X <= @SECOND 
   ORDER BY X

你试图将“简化”转化为一个语句，这种直觉会让你走上错误的道路。结果是文本减少了，但由于次优计划，执行时间却更长。我回答开头链接的文章详细介绍了这个主题。

- Remus Rusanu

Answer 5

问题在于CASE返回一个值，而不是逻辑上的分支。OMG提供的链接几乎是这方面的权威来源（Erland Sommarskog的任何建议都很好）。

链接的快速摘要：

您可以使用动态SQL来构建基于条件的语句。这通常是最佳性能的方法之一，但存在缺点。最大的缺点之一是可能存在安全问题，因此请确保您充分了解SQL注入攻击以及如何防止它们。

另一种方法是在WHERE语句中使用包含OR的复杂逻辑。在您的情况下，它应该像以下内容一样。这种方法比动态SQL简单得多且更安全，但性能可能并不总是很好。如果对于您的情况性能良好（进行测试），则请坚持使用这种方法。

SELECT
    *    -- I assume that you used * just as an example and don't actually use this in your production code
FROM
    Bank_Detail
WHERE -- POSSIBLY SOME OTHER WHERE CLAUSES
    (@second IS NULL AND X = @first) OR
    (@second IS NOT NULL AND (x >= @first AND x <= @second))
    -- POSSIBLY SOME MORE WHERE CLAUSES
ORDER BY
    x

我突然想到了另一种组织语句的方式...

SELECT
    *
FROM
    Bank_Detail
WHERE -- POSSIBLY SOME OTHER WHERE CLAUSES
    x >= @first AND
    x <= COALESCE(@second, @first)
    -- POSSIBLY SOME MORE WHERE CLAUSES
ORDER BY
    x

我还没有测试过，但我认为这将在逻辑上等效，并可能给您一个更一致的查询计划。

Erland 还提供了其他几种可能的方法，请务必阅读他关于此主题的完整文章。

- Tom H

---

尽管第二个版本有一些吸引人之处，但我认为它包含了一些不需要的行，当@second is null时，其中x > @first。 - dmc

当 @second 为 null 时，where 子句简化为 x >= @first AND x <= @first - 这与所需的 x = @first 等效。 - Steve Mayne