SQL - WHERE 中的 CASE 表达式

SQL - CASE expression inside WHERE

提问by casperOne

I read about using the CASE expression inside the WHERE clause here:

我在这里阅读了有关在 WHERE 子句中使用 CASE 表达式的信息：

http://scottelkin.com/sql/using-a-case-statement-in-a-sql-where-clause/

http://scottelkin.com/sql/using-a-case-statement-in-a-sql-where-clause/

I'm trying to use this to filter results from my select statement, based on a contract number which will be passed in by the user's application. My code currently throws an error of 'Invalid parameter' no matter what is passed in. I verified SELECT/FROM are working fine, as where as a WHERE clause without a CASE expression. Here is my code.

我正在尝试使用它来过滤来自我的 select 语句的结果，基于将由用户的应用程序传入的合同号。无论传入什么，我的代码当前都会抛出“无效参数”错误。我验证了 SELECT/FROM 工作正常，就像没有 CASE 表达式的 WHERE 子句一样。这是我的代码。

WHERE     (CASE WHEN @ContractNo = 0 THEN @ContractNo ELSE @ContractNo END =
tblContracts.ContractNo)

The redundancy of the code is for troubleshooting purposes, I'm planning on using wildcard filtering on the CASE later. I'm focusing on getting the syntax down right now. I believe this should return all records for which the parameter matches the contract number stored in the table. Any help or advice would be greatly appreciated.

代码的冗余是为了排除故障，我打算稍后在 CASE 上使用通配符过滤。我现在专注于降低语法。我相信这应该返回参数与存储在表中的合同号匹配的所有记录。任何帮助或建议将不胜感激。

采纳答案by recursive

After reading your explanation, there's a better way to do this without CASE:

阅读您的解释后，有一种更好的方法可以做到这一点CASE：

WHERE @ContractNo = 0 OR tblContracts.ContractNo = @ContractNo

This will return only matching contract numbers, unless @ContractNois 0, in which case it will return all records.

这将只返回匹配的合同编号，除非@ContractNo是 0，在这种情况下它将返回所有记录。

Edit:I've just noticed that casperOne proposed the same thing. I didn't see that. Big up yourself.

编辑：我刚刚注意到casperOne 提出了同样的事情。我没有看到。自己做大。

回答by casperOne

Are you sure you want to do this? Your case statement ALWAYS returns @ContractNo. I think what you are looking for is this:

你确定要这么做吗？您的 case 语句总是返回@ContractNo. 我想你要找的是这个：

where 
    case @ContractNo 
        when 0 then tblContracts.ContractNo 
        else @ContractNo 
    end = tblContracts.ContractNo

The filter above says "give me the contract where the ContractNoequals the parameter, or all of them if the parameter is 0.

上面的过滤器说“给我一个ContractNo等于参数的合约，或者如果参数为 0，给我所有的合约。

The previous filter only filtered where the contract number field is exactly equal to the parameter.

前面的过滤器只过滤合同编号字段与参数完全相等的地方。

Regardless, you should do this instead:

无论如何，你应该这样做：

where @ContractNo = 0 or @ContractNo = tblContracts.ContractNo

The logic is much easier to understand, and on top of that (don't quote me on this), the optimizer probably will work better outside of the case statement.

逻辑更容易理解，除此之外（不要引用我的话），优化器可能会在 case 语句之外更好地工作。

回答by dkretz

Try leaving out the parentheses which are in the wrong place anyway - the right one should be after "END".

无论如何，请尝试忽略错误位置的括号 - 正确的应该在“END”之后。

回答by Manu

Maybe you forgot to declare @ContractNo? Is it comparable to 0 and to tblContracts.ContractNo?

也许你忘了声明@ContractNo？它是否与 0 和 tblContracts.ContractNo 相当？

回答by BenAlabaster

Move your close parenthesis to before the = like so:

将右括号移到 = 之前，如下所示：

WHERE     (CASE WHEN @ContractNo = 0 THEN @ContractNo ELSE @ContractNo END)=tblContracts.ContractNo

I fail to see what this case statement will do though... you're returning the same thing in the event the @ContractNo = 0 or if it's not...

我看不出这个 case 语句会做什么……如果 @ContractNo = 0 或者不是……

The correct syntax is:

正确的语法是：

  Select...
  ...
  Where(
    Case
      When <Condition>
        Then <Return if true>
        Else <Return if false>
      End
 ) = <Whatever is being matched to the output of the case statement>

Regardless of the syntax though, your example doesn't make a lot of sense, if you're looking for all items that match or have a Contract Number of 0, then you would do:

不管语法如何，您的示例都没有多大意义，如果您要查找匹配或合同编号为 0 的所有项目，那么您将执行以下操作：

Select...
...
Where (
  @ContractNo = 0 Or
  @ContractNo = tblContracts.ContractNo
)

Which seems to make far more sense than what you're attempting to use the case statement for.

这似乎比您尝试使用 case 语句的目的更有意义。

Edit:I must've misread the question slightly - the missing param usually means that the parameter (in this case @ContractNo) is not declared in the scope of your query/procedure. But someone already pointed that out, so I can't take any credit for that.

编辑：我一定是稍微误读了这个问题——缺少的参数通常意味着参数（在这种情况下@ContractNo）没有在您的查询/过程的范围内声明。但是有人已经指出了这一点，所以我不能因此而受到赞扬。

回答by BenAlabaster

Recursive's post solved my issue precisely.

Recursive 的帖子准确地解决了我的问题。

I saw complaints about the clarity of my original post. In the future, what can I do to make what I'm saying more straight forward? I'm not used to phrasing questions about code, and apologize for any muddled things it had. Did I just need to provide the extended details like in my 2nd post?

我看到有人抱怨我的原始帖子的清晰度。将来，我该怎么做才能使我所说的更加直截了当？我不习惯用措辞表达有关代码的问题，并为它的任何混乱的事情道歉。我是否只需要像在第二篇文章中那样提供扩展的详细信息？

Thanks again for all the help.

再次感谢所有的帮助。

回答by BenAlabaster

The reason for the case statement, including the whole "If it's 0, give the parameter, and otherwise, just give the parameter" was to test it to try to get the syntax right. Originally, I had tried saying "If it's 0, then pass in '%', to return every value. The code I posted in there was because I kept getting 'Invalid Parameter' and figured there must be something wrong with my syntax. When I separated it into basic parameter matching like so,

case 语句的原因，包括整个“如果它是 0，给出参数，否则，只给出参数”是为了测试它以尝试获得正确的语法。最初，我曾尝试说“如果它是 0，则传入 '%'，以返回每个值。我在那里发布的代码是因为我不断收到“无效参数”并认为我的语法一定有问题。当我把它分成基本的参数匹配，就像这样，

WHERE @ContractNo = tblContracts.ContractNo

it returned records fine. Let me explain a bit more.

它返回的记录很好。让我再解释一下。

I'm pulling from a bunch of different tables, and filtering the content with information not included in the select statement (i.e. tblContracts is not having information pulled from it by Select, it's only used in Where). The user will select from a combo box which will have the different contract numbers, as well as a default value of 'All'.

我从一堆不同的表中提取，并使用未包含在 select 语句中的信息过滤内容（即 tblContracts 没有从 Select 中提取信息，它仅在 Where 中使用）。用户将从具有不同合同编号以及默认值“全部”的组合框中进行选择。

I'm going to have an event for when the index of the combo box changes. If it's 'All', 0 will be passed in as a parameter and I want no filtering done. Otherwise, I just want the information for that contract number (the reason for Else @ContractNo).

当组合框的索引发生变化时，我将有一个事件。如果它是“全部”，则 0 将作为参数传入，并且我不希望进行任何过滤。否则，我只想要该合同号的信息（Else @ContractNo 的原因）。

回答by JosephStyons

This syntax should work (it does in Oracle)

这种语法应该有效（它在 Oracle 中有效）

WHERE CASE WHEN tblContracts.ContractNo = 0 
           THEN @ContractNo 
           ELSE tblContracts.ContractNo
      END = tblContracts.ContractNo

回答by Richard L

when you say:

当你说：

I'm pulling from a bunch of different tables, and filtering the content with information not included in the select statement (i.e. tblContracts is not having information pulled from it by Select, it's only used in Where). The user will select from a combo box which will have the different contract numbers, as well as a default value of 'All'.

我从一堆不同的表中提取，并使用未包含在 select 语句中的信息过滤内容（即 tblContracts 没有从 Select 中提取信息，它仅在 Where 中使用）。用户将从具有不同合同编号以及默认值“全部”的组合框中进行选择。

Then it sounds to me that should have an "Where exists" clause. since your not pulling any info out of that table?!

然后在我看来应该有一个“Where exists”子句。因为您没有从该表中提取任何信息？！

回答by Richard L

Don't you mean something like this?

你不是说这样的吗？

SELECT * 
    FROM tblContracts
    WHERE     
    CASE 
       WHEN tblContracts.ContractNo = 0 THEN @ContractNo 
       ELSE tblContracts.ContractNo
    END = tblContracts.ContractNo

Where @ContractNo is variable of the same datatype as tblContracts.ContractNo

其中@ContractNo 是与 tblContracts.ContractNo 具有相同数据类型的变量