如何在SQL Server临时表中基于多状态码添加首次配送尝试日期列

核心问题分析 #

不同承运商可能共用相同的SHIP_REFERENCE，仅靠该字段关联会导致一对多匹配，产生重复条目。必须同时结合承运商唯一标识（如CARRIER_CODE/CARRIER_ID）进行分组和关联，才能确保每个订单对应唯一的首次配送尝试日期。

步骤1：重构临时表#first_attempt_to_deliver #

修改临时表的创建逻辑，按SHIP_REFERENCE + 承运商标识分组，筛选出对应承运商下该参考号的首次配送尝试日期（状态码需根据实际业务调整）：

-- 清理已存在的临时表（若有）
IF OBJECT_ID('tempdb..#first_attempt_to_deliver') IS NOT NULL
    DROP TABLE #first_attempt_to_deliver;

-- 创建双字段分组的临时表，确保唯一性
SELECT 
    osc.SHIP_REFERENCE,
    osc.CARRIER_CODE, -- 替换为实际的承运商唯一标识字段
    MIN(osc.STATUS_DATE) AS FIRST_ATTEMPT_TO_DELIVER
INTO #first_attempt_to_deliver
FROM ORDER_STATUS_CARRIER osc
WHERE osc.STATUS_CODE = 'ATTEMPTED' -- 替换为业务中代表首次配送尝试的状态码
GROUP BY osc.SHIP_REFERENCE, osc.CARRIER_CODE;

步骤2：为Table_A添加新列并关联更新 #

先添加目标列，再通过SHIP_REFERENCE + 承运商标识双字段关联临时表，避免重复匹配：

-- 1. 新增存储首次尝试日期的列
ALTER TABLE Table_A
ADD FIRST_ATTEMPT_TO_DELIVER DATE;

-- 2. 双字段关联更新，确保每个Table_A行匹配唯一结果
UPDATE ta
SET ta.FIRST_ATTEMPT_TO_DELIVER = fatd.FIRST_ATTEMPT_TO_DELIVER
FROM Table_A ta
JOIN #first_attempt_to_deliver fatd
    ON ta.SHIP_REFERENCE = fatd.SHIP_REFERENCE
    AND ta.CARRIER_CODE = fatd.CARRIER_CODE; -- 关键：同时匹配承运商标识

额外说明 #

• 若Table_A未直接存储承运商标识，需先通过订单ID等字段关联获取对应承运商信息，再调整临时表和关联逻辑。
• MIN(STATUS_DATE)用于提取同一承运商+参考号组合下的最早尝试日期，符合“首次尝试”的业务需求。
• 状态码ATTEMPTED需替换为系统中实际标记配送尝试的代码（如DELIVERY_ATTEMPT_1等）。

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