如何在SQL Server中为关联的Contact与Email值生成相同GUID？

问题描述 #

现有如下数据：

需求是：基于valueRulesContact与valueRuleEmail的关联关系（共享同一contact或email的行属于同一组），为每组生成相同的GUID。例如：

• 19702229029lori + loripivonkacomlori与119702229029lori + loripivonkacomlori共享同一email，需归为同一组，使用相同GUID；
• 119702229029lori + manorscomlori与上述两行共享同一contact，也需归为同一组，使用相同GUID。

期望输出：

请问如何在SQL Server中实现？

解决方案 #

这是典型的连通分量识别问题，可通过递归CTE（公共表表达式）遍历所有关联的contact和email，为每个连通分量分配唯一标识。以下是具体实现：

方法1：生成随机GUID

-- 假设原表名为YourTable
WITH AllNodes AS (
    -- 提取所有唯一的contact和email作为节点
    SELECT valueRulesContact AS Node FROM YourTable
    UNION
    SELECT valueRuleEmail AS Node FROM YourTable
),
RecursiveCTE AS (
    -- 初始化：每个节点的初始组ID为自身
    SELECT 
        Node,
        Node AS GroupID,
        CAST(Node AS VARCHAR(MAX)) AS VisitedNodes
    FROM AllNodes
    UNION ALL
    -- 递归遍历：合并所有关联的节点组
    SELECT 
        a.Node,
        MIN(r.GroupID) AS GroupID,
        CONCAT(r.VisitedNodes, ',', a.Node) AS VisitedNodes
    FROM RecursiveCTE r
    JOIN YourTable t ON 
        r.Node = t.valueRulesContact OR r.Node = t.valueRuleEmail
    JOIN AllNodes a ON 
        a.Node = t.valueRulesContact OR a.Node = t.valueRuleEmail
    WHERE CHARINDEX(',' + a.Node + ',', ',' + r.VisitedNodes + ',') = 0
    GROUP BY a.Node, r.VisitedNodes
),
GroupedNodes AS (
    -- 为每个节点确定最终的组ID
    SELECT Node, MIN(GroupID) AS GroupID FROM RecursiveCTE GROUP BY Node
),
GroupGUIDs AS (
    -- 为每个组生成唯一GUID
    SELECT DISTINCT GroupID, NEWID() AS GUID FROM GroupedNodes
)
-- 关联原表输出结果
SELECT 
    t.valueRulesContact,
    t.valueRuleEmail,
    gg.GUID
FROM YourTable t
JOIN GroupedNodes gn1 ON t.valueRulesContact = gn1.Node
JOIN GroupGUIDs gg ON gn1.GroupID = gg.GroupID
ORDER BY t.valueRulesContact, t.valueRuleEmail;

方法2：生成可读的分组标识（如UNIQUE1、UNIQUE2）

如果需要示例中的可读性标识，替换GroupGUIDs部分即可：

GroupGUIDs AS (
    -- 生成有序的可读分组标识
    SELECT DISTINCT 
        GroupID,
        'UNIQUE' + CAST(ROW_NUMBER() OVER (ORDER BY GroupID) AS VARCHAR) AS GUID
    FROM GroupedNodes
)

代码说明 #

1. AllNodes：提取所有唯一的contact和email，统一作为图的节点。
2. RecursiveCTE：递归遍历每个节点的关联节点，逐步合并连通的节点组，用最小的节点值作为组标识。
3. GroupedNodes：确保每个节点只保留最终的组ID（取最小的组标识）。
4. GroupGUIDs：为每个组分配唯一标识，可选随机GUID或可读序号。
5. 最后将原表与分组结果关联，得到每行对应的分组标识。

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