SQL MSSQL 上 WHERE 子句中的 CASE 语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5764992/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CASE syntax inside a WHERE clause on MSSQL
提问by roncansan
I have the following SQL syntax on MSSQL
我在 MSSQL 上有以下 SQL 语法
SELECT
id,
firstName,
lastName
FROM
Person
WHERE
((CASE WHEN @Filter = 'firstName' THEN @Value END) = firstName ) or
((CASE WHEN @Filter = 'lastName' THEN @Value END) = lastName )
It's working but I don't know if there is a better and more efficient way to do this.
它正在工作，但我不知道是否有更好，更有效的方法来做到这一点。
Thanks in advanceName
提前致谢姓名
回答by krubo
In this example you don't really need CASEat all. ANDand ORwill run faster:
在这个例子中，你根本不需要CASE。 AND并且OR会运行得更快：
SELECT
id,
firstName,
lastName
FROM
Person
WHERE
(@Filter = 'firstName' AND @Value = firstName) OR
(@Filter = 'lastName' AND @Value = lastName)
回答by harpo
This may compile, but it seems like something's missing. @Filterapparently represents the fieldyou're filtering on, and one would expect to see a valueto check against the field, like so
这可能会编译，但似乎缺少某些东西。 @Filter显然代表您正在过滤的字段，并且人们希望看到一个值来检查该字段，就像这样
SELECT
id,
firstName,
lastName
FROM
Person
WHERE @Value =
CASE WHEN @Filter = 'firstName' THEN firstName
WHEN @Filter = 'lastName' THEN lastName
END
If this is your intent, then only one CASEexpression is needed, regardless of the number of possible filter fields.
如果这是您的意图，那么CASE无论可能的过滤器字段的数量如何，都只需要一个表达式。
Either way, the CASEstatement is going to be very efficient. SQL Server will see that the WHENexpressions are comparing scalar values and optimize accordingly (in other words, the value of @Filterdoes not need to be re-evaluated for every row).
无论哪种方式，该CASE语句都将非常有效。SQL Server 将看到WHEN表达式正在比较标量值并相应地进行优化（换句话说，@Filter不需要为每一行重新计算 的值）。
