提问



如何在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指出)[50] [51]


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

其它参考1


在这种情况下,case语句是你的朋友,并采用以下两种形式之一:


简单的案例:


SELECT CASE <variable> WHEN <value>      THEN <returnvalue>
                       WHEN <othervalue> THEN <returnthis>
                                         ELSE <returndefaultcase>
       END AS <newcolumnname>
FROM <table>


扩展案例:


SELECT CASE WHEN <test>      THEN <returnvalue>
            WHEN <othertest> THEN <returnthis>
                             ELSE <returndefaultcase>
       END AS <newcolumnname>
FROM <table>


您甚至可以将case语句放在order by子句中,以实现真正的花哨排序。

其它参考2


从SQL Server 2012开始,您可以使用 IIF 功能。[53]


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


这实际上只是一种简写(尽管不是标准的SQL)写作方式CASE


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


IIF()CASE都作为SQL语句中的表达式解析,并且只能在定义良好的位置使用。



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



如果这些限制无法满足您的需求(例如需要根据某些条件返回不同形状的结果集),那么SQL Server也会有一个程序IF关键字。[54]


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


但是,有时必须注意避免这种方法的参数嗅探问题。[55]

其它参考3


您可以在 SQL CASE语句的强大中找到一些不错的示例,我认为您可以使用的语句将是这样的(来自4guysfromrolla):[56] [57]


SELECT
    FirstName, LastName,
    Salary, DOB,
    CASE Gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
    END
FROM Employees

其它参考4


使用CASE。像这样的东西。


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

其它参考5


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

其它参考6


 SELECT
   CASE 
      WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE' 
      ELSE 'FALSE' 
   END AS Salable,
   * 
FROM PRODUCT

其它参考7


Microsoft SQL Server(T-SQL)


在选择使用中:


select case when Obsolete = 'N' or InStock = 'Y' then 'YES' else 'NO' end


在where子句中,使用:


where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end

其它参考8


通过这个链接,我们可以在T-SQL中理解IF THEN ELSE:[58]


IF EXISTS(SELECT *
          FROM   Northwind.dbo.Customers
          WHERE  CustomerId = 'ALFKI')
  PRINT 'Need to update Customer Record ALFKI'
ELSE
  PRINT 'Need to add Customer Record ALFKI'

IF EXISTS(SELECT *
          FROM   Northwind.dbo.Customers
          WHERE  CustomerId = 'LARSE')
  PRINT 'Need to update Customer Record LARSE'
ELSE
  PRINT 'Need to add Customer Record LARSE' 


这对T-SQL来说还不够好吗?

其它参考9


SQL中的简单if-else语句 服务器:


DECLARE @val INT;
SET @val = 15;

IF @val < 25
PRINT 'Hi Ravi Anand';
ELSE
PRINT 'By Ravi Anand.';

GO


在SQL Server中嵌套If ... else语句 -


DECLARE @val INT;
SET @val = 15;

IF @val < 25
PRINT 'Hi Ravi Anand.';
ELSE
BEGIN
IF @val < 50
  PRINT 'what''s up?';
ELSE
  PRINT 'Bye Ravi Anand.';
END;

GO

其它参考10


使用CASE声明:


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

etc...

其它参考11


使用纯位逻辑:


DECLARE @Product TABLE (
    id INT PRIMARY KEY IDENTITY NOT NULL
   ,Obsolote CHAR(1)
   ,Instock CHAR(1)
)

INSERT INTO @Product ([Obsolote], [Instock])
    VALUES ('N', 'N'), ('N', 'Y'), ('Y', 'Y'), ('Y', 'N')

;
WITH cte
AS
(
    SELECT
        'CheckIfInstock' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Instock], 'Y'), 1), 'N'), 0) AS BIT)
       ,'CheckIfObsolote' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Obsolote], 'N'), 0), 'Y'), 1) AS BIT)
       ,*
    FROM
        @Product AS p
)
SELECT
    'Salable' = c.[CheckIfInstock] & ~c.[CheckIfObsolote]
   ,*
FROM
    [cte] c


参见工作演示:如果没有MSSQL中的情况[59]


首先,您需要计算所选条件的truefalse的值。这里有两个NULLIF:[60]


for true: ISNULL(NULLIF(p.[Instock], 'Y'), 1)
for false: ISNULL(NULLIF(p.[Instock], 'N'), 0)


组合在一起得到1或0.接下来使用按位运算符。[61]


这是最神圣的所有方法。[62]

其它参考12


在SQL Server 2012中添加了一个新功能IIF(我们可以简单地使用):[63]


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

其它参考13


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' )

其它参考14


SELECT CASE WHEN profile.nrefillno = 0 THEN 'N' ELSE 'R'END as newref
From profile

其它参考15


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

其它参考16


这不是一个答案,只是我工作中使用的CASE语句的一个例子。它有一个嵌套的CASE语句。现在你知道为什么我的眼睛被交叉了。


 CASE orweb2.dbo.Inventory.RegulatingAgencyName
    WHEN 'Region 1'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'Region 2'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'Region 3'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'DEPT OF AGRICULTURE'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactAg
    ELSE (
            CASE orweb2.dbo.CountyStateAgContactInfo.IsContract
                WHEN 1
                    THEN orweb2.dbo.CountyStateAgContactInfo.ContactCounty
                ELSE orweb2.dbo.CountyStateAgContactInfo.ContactState
                END
            )
    END AS [County Contact Name]

其它参考17


如果您是第一次将结果插入表中,而不是将结果从一个表传送到另一个表,则可以在Oracle 11.2g中使用:


INSERT INTO customers (last_name, first_name, city)
    SELECT 'Doe', 'John', 'Chicago' FROM dual
    WHERE NOT EXISTS 
        (SELECT '1' from customers 
            where last_name = 'Doe' 
            and first_name = 'John'
            and city = 'Chicago');

其它参考18


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

其它参考19


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


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

其它参考20


作为CASE语句的替代解决方案,可以使用表驱动方法。


DECLARE @Product TABLE (ID INT, Obsolete VARCHAR(10), InStock VARCHAR(10)) 
INSERT INTO @Product VALUES
(1,'N','Y'),
(2,'A','B'),
(3,'N','B'),
(4,'A','Y')

SELECT P.* , ISNULL(Stmt.Saleable,0) Saleable
FROM 
    @Product P
    LEFT JOIN 
        ( VALUES
            ( 'N', 'Y', 1 )
        ) Stmt (Obsolete, InStock, Saleable)
        ON  P.InStock = Stmt.InStock OR P.Obsolete = Stmt.Obsolete 


结果:


ID          Obsolete   InStock    Saleable
----------- ---------- ---------- -----------
1           N          Y          1
2           A          B          0
3           N          B          1
4           A          Y          1

其它参考21


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