如何在SQL Server中依据相同Details列填充缺失的Customer ID

测试数据 #

解决方法 #

方法1：窗口函数查询补全 #

按Details分组，取组内非空的Customer值填充缺失行，适合仅需查询结果的场景：

SELECT
    COALESCE(Customer, MAX(Customer) OVER (PARTITION BY Details)) AS Customer,
    Details,
    Date,
    Amount
FROM YourTableName;

方法2：子查询关联更新表数据 #

直接修改表中缺失的Customer值，先通过子查询获取每个Details对应的有效Customer：

UPDATE t1
SET t1.Customer = t2.Customer
FROM YourTableName t1
JOIN (
    SELECT Details, MAX(Customer) AS Customer
    FROM YourTableName
    WHERE Customer IS NOT NULL
    GROUP BY Details
) t2 ON t1.Details = t2.Details
WHERE t1.Customer IS NULL;

方法3：CTE（公共表表达式）更新 #

用CTE提炼有效Customer逻辑，再执行更新，可读性更强：

WITH ValidCustomer AS (
    SELECT Details, MAX(Customer) AS Customer
    FROM YourTableName
    WHERE Customer IS NOT NULL
    GROUP BY Details
)
UPDATE t
SET t.Customer = vc.Customer
FROM YourTableName t
JOIN ValidCustomer vc ON t.Details = vc.Details
WHERE t.Customer IS NULL;

说明：

• 用MAX()或MIN()均可，因同一Details对应的Customer值一致，结果无差异。
• 若某Details对应的所有Customer都为空，这类行无法自动补全，需额外处理（如设置默认值或标记）。

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