SQL: CASE Statement

1.CASE 写法如下：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

　　

解释：1）先匹配第一条，不匹配的话继续第二条，如此循环，只要找到匹配的就终止。如果都没有匹配的，就返回 ELSE里的语句，

　　　2）如果都没匹配，而且没有ELSE语句就返回NULL值。

2.举例：

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;

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

以上例子等摘自：https://www.w3schools.com/sql/sql_case.asp

自己学习总结：

例子一：

ATime = uld.ATime == null ? DateTime.MinValue : uld.ATime

以上LINQ语句转换成SQL语句如下：

CASE 
    WHEN (uld.ATIME IS NULL) THEN GETDATE()
    ELSE uld.ADTIME 
END AS ATIME,

例子二：

IsDisabled = ul.IsDisabled || l.IsDisabled

以上LINQ语句转换成SQL语句如下：

 　CASE 
        WHEN ([ul].[IsDisabled] = 1 OR [l].[IsDisabled] = 1) THEN cast(1 as bit) 
        WHEN ( NOT ([ul].[IsDisabled] = 1 OR [l].[IsDisabled] = 1)) THEN cast(0 as bit) 
    END AS [IsDisabled]