如何用关联查询结果替换TSQL中某列的NULL值？

原查询返回结果中，t1表的Variable2列存在部分NULL值，需通过关联t2表的AccountNumber（二者含义一致）替换这些NULL值，关联规则为通过RouterCallKey匹配，同时满足指定筛选条件。

原查询语句：

select SUBSTRING([Variable2],11,10), (select
Case
When Variable1 IS NULL Then 'ANSWERED NO'
When Variable1 = 'Confirmed' Then 'CONFIRMED'
When Variable1 = 'Cancelled' Then 'CANCEL'
END) from t1 
where DialedNumberString in (20062,20063,20065)
and DateTime between CAST(CAST(GETDATE() AS DATE) AS DATETIME) and getdate()
and Variable1 IN ('Confirmed','Cancelled')
UNION ALL
(select SUBSTRING([AccountNumber],11,10),(select
Case
When CallResult = 2 Then 'END DIAL TIM'
When CallResult = 6 Then 'BUSY'
When CallResult = 8 Then 'NO ANSWER'
When CallResult = 9 Then 'HANG UP'
END As CallStatus ) from t2
where DateTime between CAST(CAST(GETDATE() AS DATE) AS DATETIME) and getdate() 
and CampaignID in (5004,5011,5012)
and QueryRuleID in (5016,5020,5021)

修改后的查询语句（已实现NULL值替换）：

SELECT 
    SUBSTRING(COALESCE(r.Variable2, d.AccountNumber), 11, 10),
    CASE
        WHEN r.Variable1 IS NULL THEN 'ANSWERED NO'
        WHEN r.Variable1 = 'Confirmed' THEN 'CONFIRMED'
        WHEN r.Variable1 = 'Cancelled' THEN 'CANCEL'
    END
FROM t1 r
LEFT JOIN t2 d 
    ON r.RouterCallKey = d.RouterCallKey
    AND d.CampaignID IN (5004,5011,5012)
    AND d.QueryRuleID IN (5016,5020,5021)
    AND d.DateTime BETWEEN CAST(CAST(GETDATE() AS DATE) AS DATETIME) AND GETDATE()
WHERE r.DialedNumberString IN (20062,20063,20065)
    AND r.DateTime BETWEEN CAST(CAST(GETDATE() AS DATE) AS DATETIME) AND GETDATE()
    AND r.Variable1 IN ('Confirmed','Cancelled')
UNION ALL
SELECT 
    SUBSTRING([AccountNumber],11,10),
    CASE
        WHEN CallResult = 2 THEN 'END DIAL TIM'
        WHEN CallResult = 6 THEN 'BUSY'
        WHEN CallResult = 8 THEN 'NO ANSWER'
        WHEN CallResult = 9 THEN 'HANG UP'
    END AS CallStatus
FROM t2
WHERE DateTime BETWEEN CAST(CAST(GETDATE() AS DATE) AS DATETIME) AND GETDATE() 
    AND CampaignID IN (5004,5011,5012)
    AND QueryRuleID IN (5016,5020,5021)

关键修改说明： #

• 使用COALESCE(r.Variable2, d.AccountNumber)函数，优先取t1的Variable2值，为NULL时自动替换为t2匹配的AccountNumber
• 将t1与t2通过RouterCallKey做LEFT JOIN，确保t1所有符合条件的行都被保留，仅在匹配到t2符合条件的记录时替换NULL值
• 把t2的筛选条件放在JOIN的ON子句中，避免因过滤导致的结果集丢失
• 保留原查询的所有筛选逻辑和UNION ALL结构，确保最终结果集与原查询一致，仅修复Variable2的NULL值问题

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