Sql Server 联合与 If 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13176954/
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
Sql Server union with If condition
提问by John
I have a query like:
我有一个查询，如：
DECLARE @tmpValue
SET @tmpValue = 0 -- it will be change
SELECT * FROM Animal WHERE AniActive = 1
UNION
IF @tmpValue > 0
SELECT * FROM Animal WHERE.Active = 0
When I use like this it is giving error because of if condition. I have to use UNION because of our structure.
当我这样使用时，它会因为 if 条件而出错。由于我们的结构，我必须使用 UNION。
How can I use it with if condition?
如何在 if 条件下使用它？
Thanks,
John
谢谢，
约翰
回答by Mahmoud Gamal
Move the condition @tmpValue > 0to the WHEREclause like so:
将条件移动@tmpValue > 0到WHERE子句中，如下所示：
SELECT * FROM Animal WHERE AniActive = 1
UNION
SELECT * FROM Animal WHERE @tmpValue > 0 AND Active = 0
回答by Garvin
You can add your condition to the query like this. the section part of the union will simply return no results if your test condition is false:
您可以像这样将条件添加到查询中。如果您的测试条件为假，则联合的部分部分将不返回任何结果：
DECLARE @tmpValue
SET @tmpValue = 0 -- it will be change
SELECT * FROM Animal WHERE AniActive = 1
UNION
SELECT * FROM Animal WHERE.Active = 0 and @tmpValue > 0
回答by Hiren Dhaduk
Best way to put condition in Query is CASE statement . you can put any number of condition in query . CASE statement is used to put conditional filters in Query .
在 Query 中放置条件的最佳方法是 CASE 语句。您可以在 query 中放置任意数量的条件。CASE 语句用于在 Query 中放置条件过滤器。
For EX.
对于 EX。
DECLARE @tmpValue
SET @tmpValue = 0 -- it will be change
SELECT * FROM Animal WHERE AniActive = 1
UNION
SELECT * FROM Animal
WHERE
1 = CASE WHEN @tmpValue = 0 THEN 0 ELSE Active = 1 END
your situation is not to complex but for more complex condition you can use nested CASE statement in Query .
您的情况并不复杂，但对于更复杂的情况，您可以在 Query 中使用嵌套的 CASE 语句。
回答by Bryan Cooper
Here is way to do it that simplifies the code by getting rid of the UNION altogether. I always prefer a simpler solution where possible. This will also perform better if the select does a table scan, since it will only scan it once rather than (potentially) twice.
这是通过完全摆脱 UNION 来简化代码的方法。在可能的情况下，我总是更喜欢更简单的解决方案。如果 select 执行表扫描，这也会表现得更好，因为它只会扫描一次而不是（可能）两次。
DECLARE @tmpValue
SET @tmpValue = 0 -- it will be change
SELECT * FROM Animal WHERE AniActive = 1 OR @tmpValue > 0
