WHERE 中的案例，SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10191424/
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 in WHERE, SQL Server
提问by el ninho
I'm doing some search, where users are choosing in dropdown some clauses. When they leave some box empty, I want query to ignore clause. I know CASE, and best I thought of is that if I pass 0 to parameter in stored procedure, it ignores that parameter, like this.
我正在做一些搜索，用户在下拉菜单中选择一些条款。当他们将一些框留空时，我希望查询忽略子句。我知道 CASE，我想到的最好的是，如果我在存储过程中将 0 传递给参数，它会忽略该参数，就像这样。
WHERE a.Country = (CASE WHEN @Country > 0 THEN @Country ELSE (something else) END)
so, (something else) should be like no condition, it can be '>0' as country ids are from all >1, but I don't know how to use > and = in same CASE.
所以，（别的东西）应该没有条件，它可以是“>0”，因为国家/地区ID来自所有>1，但我不知道如何在同一个案例中使用>和=。
Any suggestions?
有什么建议？
回答by AdaTheDev
A few ways:
几种方式：
-- Do the comparison, OR'd with a check on the @Country=0 case
WHERE (a.Country = @Country OR @Country = 0)
-- compare the Country field to itself
WHERE a.Country = CASE WHEN @Country > 0 THEN @Country ELSE a.Country END
Or, use a dynamically generated statement and only add in the Country condition if appropriate. This should be most efficient in the sense that you only execute a query with the conditions that actually need to apply and can result in a better execution plan if supporting indices are in place. You would need to use parameterised SQL to prevent against SQL injection.
或者，使用动态生成的语句并仅在适当时添加 Country 条件。这应该是最有效的，因为您只执行具有实际需要应用的条件的查询，并且如果支持索引到位，则可以产生更好的执行计划。您需要使用参数化 SQL 来防止 SQL 注入。
回答by Aaron Bertrand
You can simplify to:
您可以简化为：
WHERE a.Country = COALESCE(NULLIF(@Country,0), a.Country);
回答by SliverNinja - MSFT
(something else)should be a.Country
(something else)应该 a.Country
if Countryis nullable then make(something else)be a.Country OR a.Country is NULL
如果可以Country为空，则 make(something else)是a.Country OR a.Country is NULL
回答by tanathos
.. ELSE a.Country ...
I suppose
我想
回答by Mahmoud Gamal
Try this:
尝试这个：
WHERE a.Country = (CASE WHEN @Country > 0 THEN @Country ELSE a.Country END)
