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 更新时间：