时间戳

首页 > 解决方案 > 使用 SQL 根据各个组的传递/合并分配组号

sql - 使用 SQL 根据各个组的传递/合并分配组号

问题描述

考虑下表

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使用新自动生成的组号更新列。

例如，最初这些名称被预先分组为“Adam, Bob”、“Catherine, Dave”、“Eleanor”，然后是“Krampus”，这从Group 1.

Group 2然后将名称分组为“Adam”、“Bob & Catherine”、“Dave & Eleanor”，然后是“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()- 但无法找到一种方法将它们全部传递分组。

标签： sqlsql-serversql-server-2008

解决方案

这是一个图行走问题，因为 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;

这是一个 db<>fiddle。

推荐阅读