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

例如:

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

当前回答

CASE语句最接近SQL中的IF,并且在所有版本的SQL Server上都受支持。

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

如果希望结果为布尔值,则只需使用CAST运算符。如果您对int感到满意,这将起作用:

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

CASE语句可以嵌入到其他CASE语句中,甚至可以包含在聚合中。

SQL Server Denali(SQL Server 2012)添加了IIF语句,该语句也可用于访问(Martin Smith指出):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

其他回答

CASE语句最接近SQL中的IF,并且在所有版本的SQL Server上都受支持。

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

如果希望结果为布尔值,则只需使用CAST运算符。如果您对int感到满意,这将起作用:

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

CASE语句可以嵌入到其他CASE语句中,甚至可以包含在聚合中。

SQL Server Denali(SQL Server 2012)添加了IIF语句,该语句也可用于访问(Martin Smith指出):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product
SELECT  
(CASE 
     WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
                                            ELSE 'NO' 
 END) as Salable
, * 
FROM Product

使用SQL CASE与普通的If/Else语句一样。在下面的查询中,如果过时值=“N”或InStock值=“Y”,则输出将为1。否则,输出将为0。然后我们将0或1值放在Salable列下。

SELECT
      CASE
           WHEN obsolete = 'N' OR InStock = 'Y'
          THEN 1
        ELSE 0
      END AS Salable
      , *
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;

对于使用SQL Server 2012的用户来说,IIF是一个已添加的功能,可以作为Case语句的替代。

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