SQL：WHERE 子句中的 IF 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/87821/
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: IF clause within WHERE clause
提问by Bryan Roth
Is it possible to use an IFclause within a WHEREclause in MS SQL?
是否可以在 MS SQL的WHERE子句中使用IF子句？
Example:
例子：
WHERE
IF IsNumeric(@OrderNumber) = 1
OrderNumber = @OrderNumber
ELSE
OrderNumber LIKE '%' + @OrderNumber + '%'
回答by bdukes
Use a CASEstatement
UPDATE:The previous syntax (as pointed out by a few people) doesn't work. You can use CASE as follows:
使用CASE语句
更新：以前的语法（正如一些人所指出的）不起作用。您可以按如下方式使用 CASE：
WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber) = 1 THEN
@OrderNumber
ELSE
'%' + @OrderNumber
END
Or you can use an IF statement like @N. J. Reedpoints out.
或者，您可以使用@ NJ Reed指出的 IF 语句。
回答by njr101
You should be able to do this without any IF or CASE
您应该能够在没有任何 IF 或 CASE 的情况下执行此操作
WHERE
(IsNumeric(@OrderNumber) AND
(CAST OrderNumber AS VARCHAR) = (CAST @OrderNumber AS VARCHAR)
OR
(NOT IsNumeric(@OrderNumber) AND
OrderNumber LIKE ('%' + @OrderNumber))
Depending on the flavour of SQL you may need to tweak the casts on the order number to an INT or VARCHAR depending on whether implicit casts are supported.
根据 SQL 的风格，您可能需要根据是否支持隐式转换将订单号上的转换调整为 INT 或 VARCHAR。
This is a very common technique in a WHERE clause. If you want to apply some "IF" logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied.
这是 WHERE 子句中非常常见的技术。如果您想在 WHERE 子句中应用一些“IF”逻辑，您需要做的就是将带有布尔值 AND 的额外条件添加到需要应用的部分。
回答by Rivanni
You don't need a IF statement at all.
您根本不需要 IF 语句。
WHERE
(IsNumeric(@OrderNumber) = 1 AND OrderNumber = @OrderNumber)
OR (IsNumeric(@OrderNumber) = 0 AND OrderNumber LIKE '%' + @OrderNumber + '%')
回答by Euro Micelli
There isn't a good way to do this in SQL. Some approaches I have seen:
在 SQL 中没有很好的方法来做到这一点。我见过的一些方法：
1) Use CASE combined with boolean operators:
1) 使用 CASE 结合布尔运算符：
WHERE
OrderNumber = CASE
WHEN (IsNumeric(@OrderNumber) = 1)
THEN CONVERT(INT, @OrderNumber)
ELSE -9999 -- Some numeric value that just cannot exist in the column
END
OR
FirstName LIKE CASE
WHEN (IsNumeric(@OrderNumber) = 0)
THEN '%' + @OrderNumber
ELSE ''
END
2) Use IF's outside the SELECT
2) 在 SELECT 之外使用 IF
IF (IsNumeric(@OrderNumber)) = 1
BEGIN
SELECT * FROM Table
WHERE @OrderNumber = OrderNumber
END ELSE BEGIN
SELECT * FROM Table
WHERE OrderNumber LIKE '%' + @OrderNumber
END
3) Using a long string, compose your SQL statement conditionally, and then use EXEC
3）使用长字符串，有条件地组合你的SQL语句，然后使用EXEC
The 3rd approach is hideous, but it's almost the only think that works if you have a number of variable conditions like that.
第 3 种方法很糟糕，但如果您有许多这样的可变条件，它几乎是唯一可行的方法。
回答by Jeff Martin
You want the CASE statement
你想要 CASE 语句
WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END
回答by William
I think that where...like/=...case...then... can work with Booleans. I am using T-SQL.
我认为 where...like/=...case...then... 可以使用布尔值。我正在使用 T-SQL。
Scenario: Let's say you want to get Person-30's hobbies if bool is false, and Person-42's hobbies if bool is true. (According to some, hobby-lookups comprise over 90% of business computation cycles, so pay close attn.).
场景：假设您想在 bool 为假时获得第 30 个人的爱好，如果 bool 为真，您想获得第 42 个人的爱好。（根据一些人的说法，业余爱好查找占业务计算周期的 90% 以上，因此请密切注意。）
CREATE PROCEDURE sp_Case
@bool bit
AS
SELECT Person.Hobbies
FROM Person
WHERE Person.ID =
case @bool
when 0
then 30
when 1
then 42
end;
回答by WhoIsNinja
WHERE (IsNumeric(@OrderNumber) <> 1 OR OrderNumber = @OrderNumber)
AND (IsNumber(@OrderNumber) = 1 OR OrderNumber LIKE '%'
+ @OrderNumber + '%')
回答by Majedur Rahaman
CASEStatement is better option than IFalways.
CASE语句总是比IF更好的选择。
WHERE vfl.CreatedDate >= CASE WHEN @FromDate IS NULL THEN vfl.CreatedDate ELSE @FromDate END
AND vfl.CreatedDate<=CASE WHEN @ToDate IS NULL THEN vfl.CreatedDate ELSE @ToDate END
回答by Jubayer Hossain
WHERE OrderNumber LIKE CASE WHEN IsNumeric(@OrderNumber) = 1 THEN @OrderNumber ELSE '%' + @OrderNumber END
In line case Condition will work properly.
在线情况下，条件将正常工作。
