T-SQL 2018：如何在CASE语句中根据字段值切换比较条件？

需求说明 #

需要在SELECT的CASE语句中，根据ReceivedDate字段是否为空，切换使用不同的时间差判断条件，生成DocumentPerformance字段：

• 当DocumentStatus为APPROVED且DocumentBusiness为COMMERCIAL时：
  • 如果ReceivedDate不为空，判断DATEDIFF(Hour, ReceivedDate, GETDATE()) < 5
  • 如果ReceivedDate为空，改用DATEDIFF(Hour, ProcessDate, GETDATE()) < 10
• 满足条件时返回1，否则返回0

实现代码 #

方法1：嵌套CASE语句实现 #

SELECT
    [DocumentNo],
    [DocumentType],
    CASE 
        WHEN [DocumentStatus] = 'APPROVED'
             AND [DocumentBusiness] = 'COMMERCIAL' 
             AND CASE
                    WHEN [ReceivedDate] IS NOT NULL THEN DATEDIFF(Hour, [ReceivedDate], GETDATE()) < 5
                    ELSE DATEDIFF(Hour, [ProcessDate], GETDATE()) < 10
                END
        THEN 1 
        ELSE 0
    END AS DocumentPerformance
FROM
    DocumentTbl01

方法2：使用IIF函数简化（SQL Server适用） #

SELECT
    [DocumentNo],
    [DocumentType],
    CASE 
        WHEN [DocumentStatus] = 'APPROVED'
             AND [DocumentBusiness] = 'COMMERCIAL' 
             AND IIF([ReceivedDate] IS NOT NULL, 
                    DATEDIFF(Hour, [ReceivedDate], GETDATE()) < 5, 
                    DATEDIFF(Hour, [ProcessDate], GETDATE()) < 10)
        THEN 1 
        ELSE 0
    END AS DocumentPerformance
FROM
    DocumentTbl01

注意事项 #

• SQL中判断字符串相等使用=而非IS，IS仅用于判断NULL值，因此将原伪代码中的[DocumentStatus] IS 'APPROVED'修正为[DocumentStatus] = 'APPROVED'
• 两种方法逻辑完全一致，可根据个人习惯选择使用

内容的提问来源于stack exchange，提问作者user149104