SQL Server中按姓名/电话/邮箱关联记录并分配统一UserId

原表结构及数据 #

需求说明 #

需为满足以下任一条件的记录分配同一个UserId，标识为同一用户：

• 相同的Phone
• 相同的Name
• 相同的Email

预期结果 #

解决思路及SQL实现 #

这个问题本质是连通分量识别：将通过Name/Phone/Email关联的记录归为同一组，属于图论中的连通组件问题，可通过递归CTE实现：

完整SQL代码 #

WITH AllLinks AS (
    -- 匹配相同Name的关联记录对
    SELECT DISTINCT a.RegisterId AS SourceId, b.RegisterId AS TargetId
    FROM YourTable a
    JOIN YourTable b ON a.Name = b.Name AND a.RegisterId < b.RegisterId
    WHERE a.Name IS NOT NULL
    UNION
    -- 匹配相同Phone的关联记录对
    SELECT DISTINCT a.RegisterId AS SourceId, b.RegisterId AS TargetId
    FROM YourTable a
    JOIN YourTable b ON a.Phone = b.Phone AND a.RegisterId < b.RegisterId
    WHERE a.Phone IS NOT NULL
    UNION
    -- 匹配相同Email的关联记录对
    SELECT DISTINCT a.RegisterId AS SourceId, b.RegisterId AS TargetId
    FROM YourTable a
    JOIN YourTable b ON a.Email = b.Email AND a.RegisterId < b.RegisterId
    WHERE a.Email IS NOT NULL
),
RecursiveCTE AS (
    -- 初始：每个记录自身作为组件起点
    SELECT RegisterId AS Node, RegisterId AS Root
    FROM YourTable
    UNION ALL
    -- 递归：遍历所有关联记录，继承同一根标识
    SELECT al.TargetId, rc.Root
    FROM AllLinks al
    JOIN RecursiveCTE rc ON al.SourceId = rc.Node
    WHERE al.TargetId NOT IN (SELECT Node FROM RecursiveCTE)
),
Grouped AS (
    -- 取每个记录所在组件的最小根标识作为组标记
    SELECT Node, MIN(Root) AS GroupRoot
    FROM RecursiveCTE
    GROUP BY Node
),
UserIdMapping AS (
    -- 将组标记转换为连续的UserId
    SELECT GroupRoot, DENSE_RANK() OVER (ORDER BY GroupRoot) AS UserId
    FROM Grouped
)
SELECT 
    t.RegisterId,
    t.Name,
    t.Phone,
    t.Email,
    um.UserId
FROM YourTable t
JOIN Grouped g ON t.RegisterId = g.Node
JOIN UserIdMapping um ON g.GroupRoot = um.GroupRoot
ORDER BY um.UserId, t.RegisterId;

逻辑说明 #

1. AllLinks：生成所有直接关联的记录对，用UNION去重，a.RegisterId < b.RegisterId避免生成反向重复关联。
2. RecursiveCTE：递归遍历所有连通的记录，确保同一组件内的记录共享同一个根标识。
3. Grouped：对每个记录取所在组件的最小根标识，保证同一组件的记录有统一的组标记。
4. UserIdMapping：用DENSE_RANK()将组标记转换为连续的数字UserId。

替换YourTable为实际表名即可运行，该逻辑支持多级关联（比如A和B同Phone，B和C同Email，A、B、C会被归为同一组）。

内容的提问来源于stack exchange，提问作者Jesús Méndez