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
谢谢你,约翰
4 个解决方案
#1
16
Move the condition @tmpValue > 0 to the WHERE clause like so:
将条件@tmpValue >移动到WHERE子句如下:
SELECT * FROM Animal WHERE AniActive = 1
UNION
SELECT * FROM Animal WHERE @tmpValue > 0 AND Active = 0
#2
3
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
#3
3
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 .
在查询中放置条件的最佳方式是CASE语句。您可以在查询中放置任意数量的条件。CASE语句用于在查询中放置条件过滤器。
For 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 .
您的情况并不复杂，但是对于更复杂的情况，您可以在查询中使用嵌套的CASE语句。
#4
0
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
#1
16
Move the condition @tmpValue > 0 to the WHERE clause like so:
将条件@tmpValue >移动到WHERE子句如下:
SELECT * FROM Animal WHERE AniActive = 1
UNION
SELECT * FROM Animal WHERE @tmpValue > 0 AND Active = 0
#2
3
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
#3
3
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 .
在查询中放置条件的最佳方式是CASE语句。您可以在查询中放置任意数量的条件。CASE语句用于在查询中放置条件过滤器。
For 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 .
您的情况并不复杂，但是对于更复杂的情况，您可以在查询中使用嵌套的CASE语句。
#4
0
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