标记表中重复记录时出现错误赋值的问题排查

问题根源 #

你遇到的错误赋值和标记异常，主要来自两个核心问题：

1. Row_Number排序不稳定
   你的CTE里用row_number() over (partition by FirstName,LastName, Company order by FirstName,LastName, Company)分配序号，但分组内这三个字段值完全相同，SQL Server没有明确排序依据时，会根据数据物理存储顺序或执行计划临时排序结果随机分配nr=1的记录。在100万条数据的场景下，物理顺序可能不一致，导致有时候nr=1被分配给ID更大的记录，而非你预期的最小ID记录——这就是部分记录Group字段错误赋值的原因。

2. 主记录标记逻辑依赖错误
   你第二个更新语句用WHERE b.ID = b.Group标记主记录，但如果第一个更新中Group被设置成其他记录的ID（比如示例中的ID=2），这条条件就无法匹配，主记录的Status依然是'S'，最终导致所有记录都被标记为'S'。

修正后的解决方案 #

我们可以直接通过分组取最小ID确定主记录，避免排序不稳定问题，同时一步完成Status标记：

-- 一次性完成Group字段赋值和Status标记
WITH GroupMain AS (
    SELECT 
        FirstName, LastName, Company,
        MIN(ID) AS MainRecordID
    FROM [dbo].xx
    GROUP BY FirstName, LastName, Company
)
UPDATE xx
SET 
    [Group] = gm.MainRecordID,
    Status = CASE WHEN ID = gm.MainRecordID THEN 'P' ELSE 'S' END
FROM xx
INNER JOIN GroupMain gm 
    ON xx.FirstName = gm.FirstName 
    AND xx.LastName = gm.LastName 
    AND xx.Company = gm.Company;

代码说明 #

• GroupMain CTE：通过MIN(ID)直接获取每个重复分组的主记录ID，这个值确定且唯一，不会出现随机分配问题。
• 一次性更新：在同一条语句中既设置Group字段为主记录ID，又通过CASE语句直接标记主记录为'P'、其他记录为'S'——避免了两次更新的逻辑依赖问题，还提升了执行效率。

额外注意点 #

• Group是SQL Server关键字，建议用方括号[Group]包裹，避免语法错误。
• 即使表中有NULL值（比如初始的Group/Status为NULL），这个方案依然有效，因为分组依据是FirstName/LastName/Company，只要这三个值相同就会被归为一组。

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