如何在SQL SELECT语句中执行IF…THEN?

例如:

SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

当前回答

使用CASE语句:

SELECT CASE
       WHEN (Obsolete = 'N' OR InStock = 'Y')
       THEN 'Y'
       ELSE 'N'
END as Available

etc...

其他回答

使用CASE。像这样。

SELECT Salable =
        CASE Obsolete
        WHEN 'N' THEN 1
        ELSE 0
    END

在SQL Server 2012中,您可以使用IIF函数进行此操作。

SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM   Product

这实际上只是编写CASE的一种速记(尽管不是标准SQL)方式。

与扩展的CASE版本相比,我更喜欢简洁。

IIF()和CASE都解析为SQL语句中的表达式,只能在定义良好的地方使用。

CASE表达式不能用于控制Transact-SQL语句、语句块、用户定义函数和存储过程。

如果这些限制不能满足您的需求(例如,需要根据某些条件返回不同形状的结果集),那么SQL Server也有一个过程If关键字。

IF @IncludeExtendedInformation = 1
  BEGIN
      SELECT A,B,C,X,Y,Z
      FROM   T
  END
ELSE
  BEGIN
      SELECT A,B,C
      FROM   T
  END

但是,有时必须小心避免这种方法的参数嗅探问题。

SELECT 1 AS Saleable, *
  FROM @Product
 WHERE ( Obsolete = 'N' OR InStock = 'Y' )
UNION
SELECT 0 AS Saleable, *
  FROM @Product
 WHERE NOT ( Obsolete = 'N' OR InStock = 'Y' )
case statement some what similar to if in SQL server

SELECT CASE 
            WHEN Obsolete = 'N' or InStock = 'Y' 
               THEN 1 
               ELSE 0 
       END as Saleable, * 
FROM Product

会是这样的:

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;