将每个重复行匹配到一个不同的记录

Question

当我们将新记录加载到系统中时，我们首先将它们保存在一个暂存loadedRecords表中，在那里我们尝试将它们与系统上的现有记录进行匹配。

一旦加载的记录与现有记录匹配，我们更新加载的记录以引用现有记录的id，但是有可能存在多个"相同"的记录。记录，即具有相同匹配标准的多个记录，我找不到一种方法来做到这一点，使每个现有记录与不同的加载记录相匹配。

,DB&lt的在小提琴

我现在有这个:

UPDATE loadedRecords
SET recordID = r.id
FROM loadedRecords
JOIN records as r
ON r.amount = loadedRecords.amount
WHERE loadedRecords.recordID IS NULL

但是，这会导致所有3条加载的amount为400的记录与400为amount的第一个现有记录相匹配:

<表类>id数量recordIDtbody><<tr>14001250043400144001

如果使用ROW_NUMBER()窗口函数这一简单技巧，这实际上很容易做到。使用这个函数，我们可以在新列中为每组amt值提供一个从1开始的唯一整数。下面是为您的表执行此操作的代码:

SELECT *, ROW_NUMBER() OVER (PARTITION BY amount) AS RN
FROM loadedRecords
SELECT *, ROW_NUMBER() OVER (PARTITION BY amount) AS RN
FROM records

现在我们把这两个结合起来

UPDATE loadedRecords
SET recordID = r.id
FROM loadedRecords
JOIN (
SELECT id, amount, ROW_NUMBER() OVER (PARTITION BY amount) AS RN
FROM records
) r ON r.amount = loadedRecords.amount AND
r.RN = ROW_NUMBER() OVER (PARTITION BY loadedRecords.amount)
WHERE loadedRecords.recordID IS NULL