使用 SQL 根据单个组的传递/合并分配组编号

Question

>考虑下表

create table #table 
(
Name varchar(100), 
Group1 bigint, 
Group2 bigint, 
Group3 bigint
)
insert into #table values('Adam', 276328, 00001, 0)
insert into #table values('Bob', 276328, 00002, 0)
insert into #table values('Catherine', 927356, 00002, 0)
insert into #table values('Dave', 927356, 00003, 0)
insert into #table values('Eleanor', 927379, 00003, 0)
insert into #table values('Krampus', 927390, 00004, 0)

我正在尝试使用新自动生成的组号更新Group3列。

例如，最初这些名字被预先分组为"亚当，鲍勃"，"凯瑟琳，戴夫"，"埃莉诺"，然后是"Krampus"，这从Group 1中可以明显看出。

然后Group 2将名字分组为"亚当"，"鲍勃和凯瑟琳"，"戴夫和埃莉诺"，然后是"Krampus">

我要做的是，使用组 1 和组 2 分配一个新的"组 3"，以传递方式对所有这些组进行分组。

像"Adam & Bob & Catherine& Dave & Eleanor"这样，因为 Adam 与 Bob 在第 1 组分组，而 Bob 与 Catherine 在第 2 组分组，这意味着它们都是传递分组在一起的。

输出如下：

'Adam', 276328, 00001, 00001
'Bob', 276328, 00002, 00001
'Catherine', 927356, 00002, 00001
'Dave', 927356, 00003, 00001
'Eleanor', 927379, 00003, 00001
'Krampus', 927390, 00004, 00002

我已经玩过Rank()，Dense_Rank()- 但找不到一种传递分组的方法。

Answer 1

这是一个图行走问题，因为 A 和 B 可能属于第 1 组。 然后 A 和 C 可能属于第 2 组。 那么 C 和 D 可以在第 1 组中。

这表明递归 CTE：

with cte as (
select name, group1, group2, convert(varchar(max), ',' + name + ',') as visited, name as grouping
from t
union all
select cte.name, t.group1, t.group2, concat(visited, t.name, ','),
(case when cte.name < t.name then cte.name else t.name end)
from cte join
t
on cte.group1 = t.group1 or
cte.group2 = t.group2
where visited not like '%,' + t.name + ',%'
)
select name, dense_rank() over (order by min(grouping)) as grp
from cte
group by name;

这是一个数据库<>小提琴。