SQL Server中如何实现重复加载记录与现有记录一一匹配

原有更新逻辑仅通过amount字段做等值关联，未对同金额下的多条记录做一对一的行映射，SQL Server会将所有金额匹配的加载记录全部关联到第一条符合条件的现有记录，最终出现重复匹配的问题。

使用ROW_NUMBER()窗口函数实现同金额组内的一对一匹配：

• 对暂存表中未匹配的加载记录，按amount分组，组内按任意规则排序（无顺序要求时直接按id排序即可）生成连续匹配序号
• 对现有记录表，同样按amount分组，组内按相同排序规则生成连续匹配序号
• 关联时同时匹配amount和组内序号，即可保证每条记录最多匹配一个对应项：
  • 同金额下加载记录多于现有记录时，多余加载记录无对应序号的匹配项，recordID保持NULL
  • 同金额下现有记录多于加载记录时，多余现有记录不会被关联，完全符合需求

实现代码 #

WITH cte_loaded AS (
    SELECT
        id,
        amount,
        recordID,
        ROW_NUMBER() OVER (PARTITION BY amount ORDER BY id) AS match_seq
    FROM loadedRecords
    WHERE recordID IS NULL
),
cte_records AS (
    SELECT
        id,
        amount,
        ROW_NUMBER() OVER (PARTITION BY amount ORDER BY id) AS match_seq
    FROM records
)
UPDATE cte_loaded
SET recordID = cte_records.id
FROM cte_loaded
INNER JOIN cte_records
    ON cte_loaded.amount = cte_records.amount
    AND cte_loaded.match_seq = cte_records.match_seq;

匹配效果 #

执行上述代码后，得到的匹配结果和预期完全一致：

如果出现同金额下加载记录数量多于现有记录的场景，比如4条金额400的加载记录仅对应3条400的现有记录，序号为4的加载记录找不到匹配项，recordID字段会保持NULL，满足边缘场景要求。

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