如何将关联的多组Group ID合并为单一分组ID？

原始数据集 #

;WITH CTE AS
( SELECT *
  FROM (VALUES
(1, 10, 20, 30),
(2, 10, 21, 31),
(3, 11, 21, 31),
(4, 12, 22, 32),
(5, 13, 23, 33),
(6, 14, 24, 33),
(7, 14, 25, 34),
(8, 15, 26, 36)
) AS MyValues(ID, GroupID1, GroupID2, GroupID3)
)
SELECT *
FROM CTE

需求说明 #

需要将上述数据中存在关联的分组（只要任意一个GroupID字段值相同，即视为同一大组）合并为单一分组，得到如下结果：

实现思路 #

这其实是个找连通组的问题：只要两条记录的任意一个GroupID（GroupID1/GroupID2/GroupID3）值相同，就属于同一个大组。我们可以通过递归找出所有关联的记录，再给每个大组分配唯一的ID。

完整SQL代码 #

;WITH CTE AS
( SELECT *
  FROM (VALUES
(1, 10, 20, 30),
(2, 10, 21, 31),
(3, 11, 21, 31),
(4, 12, 22, 32),
(5, 13, 23, 33),
(6, 14, 24, 33),
(7, 14, 25, 34),
(8, 15, 26, 36)
) AS MyValues(ID, GroupID1, GroupID2, GroupID3)
),
-- 拆分ID与所有GroupID的关联关系
GroupLinks AS (
    SELECT ID, GroupID1 AS GroupID FROM CTE
    UNION ALL
    SELECT ID, GroupID2 AS GroupID FROM CTE
    UNION ALL
    SELECT ID, GroupID3 AS GroupID FROM CTE
),
-- 递归查找所有关联的ID，确定连通组
RecursiveCTE AS (
    SELECT ID, GroupID, ID AS RootID
    FROM GroupLinks
    UNION ALL
    SELECT r.ID, g.GroupID, g.ID AS RootID
    FROM RecursiveCTE r
    JOIN GroupLinks g ON r.GroupID = g.GroupID
    WHERE r.RootID <> g.ID
),
-- 为每个ID确定所属组的最小标识
MinRoot AS (
    SELECT ID, MIN(RootID) AS MinRootID
    FROM RecursiveCTE
    GROUP BY ID
)
-- 生成连续的单一分组ID
SELECT 
    c.ID,
    DENSE_RANK() OVER(ORDER BY m.MinRootID) AS SingleGroupID
FROM CTE c
JOIN MinRoot m ON c.ID = m.ID
ORDER BY c.ID;

代码解释 #

1. GroupLinks：把每条记录的三个GroupID拆成单独的行，让每个ID和它关联的所有GroupID一一对应，方便后续顺着GroupID找关联的其他ID。
2. RecursiveCTE：通过递归查询，顺着GroupID找到所有相关联的ID，记录每个ID的初始RootID（自身ID），最终同一大组里的所有ID会被关联到一起。
3. MinRoot：给每个ID取关联到的最小RootID，确保同一大组内的所有ID拥有相同的标识值。
4. 最终查询：用DENSE_RANK()函数根据MinRootID生成连续的SingleGroupID，得到符合需求的结果。

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