SQL 条件语句 (IF, CASE WHEN, IFNULL)

SQL 条件语句 (IF, CASE WHEN, IFNULL)

1、IF

1.1 表达式：

IF( expr1 , expr2 , expr3 )

expr1条件，条件为true，则值是expr2 ，false，值就是expr3

示例;

SELECT 
o.id,u.account,catagory.`name`,orderTime,detail.amount,periodtime,if(direction='0','看涨','看跌') directionName
FROM t_order_detail detail
LEFT JOIN t_order o ON o.id = detail.orderId
LEFT JOIN t_catagory catagory ON catagory.id = o.catId
LEFT JOIN t_user u ON u.id = o.userId

1.2  IF ELSE 做为流程控制语句使用

IF Boolean_expression   
     { sql_statement | statement_block }   
[ ELSE   
     { sql_statement | statement_block } ] 　

参数说明：

Boolean_expression
返回True或False的表达式。如果布尔表达式包含SELECT语句，则SELECT语句必须用括号括起来

{ sql_statement | statement_block }
使用语句块定义的任何有效的Transact-SQL语句或语句分组。要定义语句块（批处理），请使用流语言关键字BEGIN和END 组合。尽管所有Transact-SQL语句在BEGIN…END块中都是有效的，但某些Transact-SQL语句不应在同一批（语句块）中组合在一起。

示例1：

IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
       SELECT 'Weekend';
ELSE 
       SELECT 'Weekday';

注意：IF作为一条语句，在END IF后需要加上分号“;”以表示语句结束，其他语句如CASE、LOOP等也是相同的。

示例2：

USE AdventureWorks2012;  
GO  
DECLARE @AvgWeight decimal(8,2), @BikeCount int  
IF   
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5  
BEGIN  
   SET @BikeCount =   
        (SELECT COUNT(*)   
         FROM Production.Product   
         WHERE Name LIKE 'Touring-3000%');  
   SET @AvgWeight =   
        (SELECT AVG(Weight)   
         FROM Production.Product   
         WHERE Name LIKE 'Touring-3000%');  
   PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.'  
   PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.';  
END  
ELSE   
BEGIN  
SET @AvgWeight =   
        (SELECT AVG(Weight)  
         FROM Production.Product   
         WHERE Name LIKE 'Touring-3000%' );  
   PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ;  
END ;  
GO

　　

2、CASE WHEN

• CASE表达式有两种格式：两种格式都支持可选的ELSE参数。
  • 简单大小写表达式将一个表达式与一组简单表达式进行比较以确定结果。
  • 搜索的CASE表达式计算一组布尔表达式以确定结果。
• CASE可用于允许有效表达式的任何语句或子句中。例如，可以在SELECT、UPDATE、DELETE和SET等语句中使用CASE，也可以在SELECTU list、in、WHERE、ORDER BY和HAVING等子句中使用CASE。

表达式：

CASE 列名
    WHEN 条件 THEN 结果 
    ELSE 其他结果
    END 别名

示例：

SELECT 
CASE detail.`status` WHEN '0' THEN '未开仓'
WHEN '1' THEN '已开仓'
WHEN '2' THEN '已平仓'
ELSE '取消订单' END
status,
CASE o.type WHEN '0' THEN '单期'
WHEN '1' THEN '多期'
ELSE '策略' END 
typeName
FROM t_order_detail detail
LEFT JOIN t_order o ON o.id = detail.orderId

示例：

SELECT CASE sva
WHEN 1 THEN '男' 
　　ELSE '女' 
END AS s 
FROM table_name
WHERE sva != '';

官方示例1（SELECT）：

USE AdventureWorks2012;  
GO  
SELECT   ProductNumber, Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   Name  
FROM Production.Product  
ORDER BY ProductNumber;  
GO

官方示例2：

USE AdventureWorks2012;  
GO  
SELECT   ProductNumber, Name, "Price Range" =   
      CASE   
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'  
         WHEN ListPrice < 50 THEN 'Under $50'  
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'  
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'  
         ELSE 'Over $1000'  
      END  
FROM Production.Product  
ORDER BY ProductNumber ;  
GO

官方示例3：

SELECT BusinessEntityID, SalariedFlag  
FROM HumanResources.Employee  
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC  
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;  
GO

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL  
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName  
         ELSE CountryRegionName END;

官方示例4：

USE AdventureWorks2012;  
GO  
UPDATE HumanResources.Employee  
SET VacationHours =   
    ( CASE  
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40  
         ELSE (VacationHours + 20.00)  
       END  
    )  
OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,   
       Inserted.VacationHours AS AfterValue  
WHERE SalariedFlag = 0;

官方示例5：　　

USE AdventureWorks2012;  
GO  
CREATE FUNCTION dbo.GetContactInformation(@BusinessEntityID int)  
    RETURNS @retContactInformation TABLE   
(  
BusinessEntityID int NOT NULL,  
FirstName nvarchar(50) NULL,  
LastName nvarchar(50) NULL,  
ContactType nvarchar(50) NULL,  
    PRIMARY KEY CLUSTERED (BusinessEntityID ASC)  
)   
AS   
-- Returns the first name, last name and contact type for the specified contact.  
BEGIN  
    DECLARE   
        @FirstName nvarchar(50),   
        @LastName nvarchar(50),   
        @ContactType nvarchar(50);  
  
    -- Get common contact information  
    SELECT   
        @BusinessEntityID = BusinessEntityID,   
@FirstName = FirstName,   
        @LastName = LastName  
    FROM Person.Person   
    WHERE BusinessEntityID = @BusinessEntityID;  
  
    SET @ContactType =   
        CASE   
            -- Check for employee  
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e   
                WHERE e.BusinessEntityID = @BusinessEntityID)   
                THEN 'Employee'  
  
            -- Check for vendor  
            WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec  
                WHERE bec.BusinessEntityID = @BusinessEntityID)   
                THEN 'Vendor'  
  
            -- Check for store  
            WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v            
                WHERE v.BusinessEntityID = @BusinessEntityID)   
                THEN 'Store Contact'  
  
            -- Check for individual consumer  
            WHEN EXISTS(SELECT * FROM Sales.Customer AS c   
                WHERE c.PersonID = @BusinessEntityID)   
                THEN 'Consumer'  
        END;  
  
    -- Return the information to the caller  
    IF @BusinessEntityID IS NOT NULL   
    BEGIN  
        INSERT @retContactInformation  
        SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType;  
    END;  
  
    RETURN;  
END;  
GO  
  
SELECT BusinessEntityID, FirstName, LastName, ContactType  
FROM dbo.GetContactInformation(2200);  
GO  
SELECT BusinessEntityID, FirstName, LastName, ContactType  
FROM dbo.GetContactInformation(5);

官方示例6：

USE AdventureWorks2012;  
GO  
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate  
FROM HumanResources.Employee AS e  
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID  
GROUP BY JobTitle  
HAVING (MAX(CASE WHEN Gender = 'M'   
        THEN ph1.Rate   
        ELSE NULL END) > 40.00  
     OR MAX(CASE WHEN Gender  = 'F'   
        THEN ph1.Rate    
        ELSE NULL END) > 42.00)  
ORDER BY MaximumRate DESC;

官方示例7：

-- Uses AdventureWorks  
  
SELECT   ProductAlternateKey, Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   EnglishProductName  
FROM dbo.DimProduct  
ORDER BY ProductKey;

官方示例8：

-- Uses AdventureWorks   
  
UPDATE dbo.DimEmployee  
SET VacationHours =   
    ( CASE  
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40  
         ELSE (VacationHours + 20.00)   
       END  
    )   
WHERE SalariedFlag = 0;

　　

3、ISNULL()、NVL()、IFNULL()、 NULLIF

3.1 表达式(MySQL)：

IFNULL( expr1 , expr2)

expr1 值不为 NULL 返回 expr1，否则返回 expr2    例如：

IFNULL(AA,0); --AA不为NULL则返回AA，否则返回 0

3.2  -- MsSQL的 ISNULL  功能一样

3.3  -- Oracle的 NVL 功能一样

3.4 表达式(MsSQL)： NULLIF 不一样

NULLIF ( expression , expression )  

 如果两个表达式不相等，则返回第一个表达式。如果表达式相等，则NULLIF返回第一个表达式类型的null值。

 

创建时间：2020.09.14　　更新时间：