如何在SQL Server中按多列任一匹配对记录分组

问题背景 #

现有表结构包含ID、C1、C2字段，需要生成带Group ID的结果集：只要某条记录的C1或C2与组内任意一条记录的C1/C2匹配，就将其归入同一组。

示例数据：

期望输出：

核心思路 #

这个问题本质是图的连通分量计算：把C1和C2看作图中的节点，每一行记录代表节点间的一条边，我们需要找出所有连通的节点集合，再将原始行映射到对应的连通组。

最优解决方案（递归CTE） #

在SQL Server中，用递归CTE可以高效实现这个逻辑，具体代码如下：

1. 创建示例表 #

CREATE TABLE #Target
(
    ID VARCHAR(MAX),
    C1 VARCHAR(MAX),
    C2 VARCHAR(MAX)
);

INSERT INTO #Target
VALUES
('1','p1','e1'),
('2','p2','e2'),
('3','p1','e2'),
('4','p3','e3'),
('5','p3','e4'),
('6','p4','e4');

2. 递归计算连通分量并生成分组 #

WITH Nodes AS (
    -- 提取所有唯一节点（C1和C2的并集）
    SELECT C1 AS Node FROM #Target
    UNION
    SELECT C2 AS Node FROM #Target
),
ConnectedComponents AS (
    -- 初始节点：每个节点自身作为根节点
    SELECT 
        Node,
        Node AS RootNode
    FROM Nodes
    UNION ALL
    -- 递归合并连通节点：通过C1-C2关联的节点共享同一根节点
    SELECT 
        n.Node,
        cc.RootNode
    FROM ConnectedComponents cc
    JOIN #Target t ON cc.Node = t.C1
    JOIN Nodes n ON t.C2 = n.Node
    WHERE n.RootNode <> cc.RootNode
    UNION ALL
    SELECT 
        n.Node,
        cc.RootNode
    FROM ConnectedComponents cc
    JOIN #Target t ON cc.Node = t.C2
    JOIN Nodes n ON t.C1 = n.Node
    WHERE n.RootNode <> cc.RootNode
),
ComponentGroups AS (
    -- 为每个连通分量分配唯一组编号
    SELECT 
        Node,
        DENSE_RANK() OVER (ORDER BY MIN(RootNode)) AS GroupNumber
    FROM ConnectedComponents
    GROUP BY Node
)
-- 关联原始表生成最终结果
SELECT 
    t.ID,
    t.C1,
    t.C2,
    'G' + CAST(cg.GroupNumber AS VARCHAR) AS GID
FROM #Target t
JOIN ComponentGroups cg ON t.C1 = cg.Node
ORDER BY t.ID;

3. 清理临时表 #

DROP TABLE #Target;

代码解释 #

• Nodes CTE：收集所有C1和C2的唯一值，作为图的节点集合。
• ConnectedComponents CTE：递归遍历所有连通节点，将同一连通分量的节点标记为同一个根节点（RootNode），确保所有关联节点归属同一组。
• ComponentGroups CTE：对每个连通分量的根节点进行排名，生成唯一的组编号。
• 最后将原始表与分组结果关联，通过C1（或C2，同一行的C1、C2必然属于同一组）映射到对应的GID。

基于你现有进展的优化方案 #

如果你已经生成了包含匹配ID集合的Groups列，可以通过递归合并有交集的ID组来实现分组，代码如下：

-- 生成临时表与关联ID集合
SELECT  *, CAST(NULL AS INT) AS ID_To
INTO #t
FROM
(
VALUES  ('1','p1','e1'),
('2','p2','e2'),
('3','p1','e2'),
('4','p3','e3'),
('5','p3','e4'),
('6','p4','e4')
) t (ID,C1,C2);

DROP TABLE IF EXISTS #Groups;
SELECT 
    ID1, 
    STRING_AGG(DISTINCT ID2, ',') + ',' + STRING_AGG(DISTINCT ID3, ',') AS GroupIDs
INTO #Groups
FROM (
    select 
        t1.ID as ID1,
        t2.ID as ID2,
        t3.ID as ID3
    from #t t1
    LEFT JOIN #t t2 ON t1.C1 = t2.C1 
    LEFT JOIN #t t3 ON t1.C2 = t3.C2
    WHERE t1.C1 = t2.C1 OR t1.C2 = t3.C2
) Groups 
GROUP BY ID1;

-- 递归合并有交集的ID组
WITH RecursiveGroups AS (
    SELECT 
        CAST(ID1 AS VARCHAR(MAX)) AS GroupMembers,
        CAST(ID1 AS VARCHAR(MAX)) AS CurrentID
    FROM #Groups
    UNION ALL
    SELECT 
        CAST(rg.GroupMembers + ',' + g.ID1 AS VARCHAR(MAX)),
        g.ID1
    FROM RecursiveGroups rg
    JOIN #Groups g ON CHARINDEX(g.ID1, rg.GroupMembers) = 0 
        AND EXISTS (
            SELECT 1 FROM STRING_SPLIT(rg.GroupMembers, ',') s
            JOIN STRING_SPLIT(g.GroupIDs, ',') s2 ON s.value = s2.value
        )
),
UniqueGroups AS (
    SELECT 
        DISTINCT 
        GroupMembers,
        DENSE_RANK() OVER (ORDER BY MIN(CurrentID)) AS GroupNumber
    FROM RecursiveGroups
    GROUP BY GroupMembers
)
-- 关联原始表得到结果
SELECT 
    t.ID,
    t.C1,
    t.C2,
    'G' + CAST(ug.GroupNumber AS VARCHAR) AS GID
FROM #t t
JOIN UniqueGroups ug ON CHARINDEX(t.ID, ug.GroupMembers) > 0
ORDER BY t.ID;

-- 清理临时表
DROP TABLE #t;
DROP TABLE #Groups;

注意：这种方法在数据量较大时性能不如递归CTE的连通分量方案，建议优先使用第一种方法。

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