SQL Server：使用LEFT JOIN为查询设置默认值，显示空分类等待时间

问题核心是LEFT JOIN后，当CALC_BLENDED_TEST中无对应分类的记录时，W.DATA_TIME等关联字段为NULL，导致LASTUPDATED计算结果为空。以下是修改后的查询语句，确保所有分类都能显示有效时间值：

SELECT
    CAST(1900 AS INT) AS SITEID, 
    CASE
        WHEN COALESCE(W.WAIT_BLENDED_S, 0) = 0 THEN '0 Minutes'
        WHEN W.WAIT_BLENDED_S = 1 THEN '1 Second'    
        WHEN W.WAIT_BLENDED_S < 3600 THEN CAST((W.WAIT_BLENDED_S / 60) AS nvarchar(10)) + ' minutes'
        WHEN W.WAIT_BLENDED_S < 3600 * 2 THEN CAST((W.WAIT_BLENDED_S / 3600) AS nvarchar(10)) + ' Hour and ' + CAST(((W.WAIT_BLENDED_S / 60) % 60) AS nvarchar(10)) + ' minutes' 
        WHEN W.WAIT_BLENDED_S >= 3600 * 2 THEN CAST((W.WAIT_BLENDED_S / 3600) AS nvarchar(10)) + ' Hours and ' + CAST(((W.WAIT_BLENDED_S / 60) % 60) AS nvarchar(10)) + ' minutes'
        ELSE 'ERROR'
    END AS VISUALVALUE,
    COALESCE(W.WAIT_BLENDED_S, 0) AS WAIT_BLENDED_S,
    -- 无匹配记录时用当前UTC时间填充，也可替换为'无更新数据'等自定义文本
    CONCAT(REPLACE(CONVERT(char(11), ISNULL(W.DATA_TIME, SYSUTCDATETIME()), 113), ' ', '/'), ' ',  FORMAT(ISNULL(W.DATA_TIME, SYSUTCDATETIME()), 'hh:mm:ss tt')) AS LASTUPDATED,
    DATEDIFF(SECOND, '19700101', SYSUTCDATETIME()) AS BATCHID
FROM 
    TRIAGE_INFORMATION C
LEFT JOIN 
    CALC_BLENDED_TEST W ON W.STANDARD_TRIAGE = C.STANDARD_TRIAGE
ORDER BY 
    C.STANDARD_TRIAGE

关键修改说明 #

• 用COALESCE(W.WAIT_BLENDED_S, 0)将空的等待秒数统一转为0，简化CASE判断逻辑，确保无数据分类显示0 Minutes
• 对W.DATA_TIME使用ISNULL(W.DATA_TIME, SYSUTCDATETIME())，当无对应记录时用当前UTC时间填充LASTUPDATED，保证该字段始终有有效时间值；若需显示文本提示，可替换为ISNULL(CONCAT(REPLACE(...)), '无更新数据')（注意保持字段类型一致）
• 保留LEFT JOIN确保TRIAGE_INFORMATION中的所有分类都能被返回

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