可以使用递归cte为每个“列”值指定一个数字。首先在两个方向上创建边，然后跟随它们：

with pairs as (
      select columnA, columnB from myTable
      union -- on purpose to remove duplicates
      select columnB, columnA from myTable
     ),
     cte as (
      select distinct columnA, columnA as columnB,
             convert(varchar(max), ',' + columnA + ',') as visited,
             1 as lev
      from pairs
      union all
      select cte.columnA, p.columnB, concat(visited, p.columnA, ','), lev + 1
      from cte join
           pairs p
           on cte.columnB = p.columnA
      where cte.visited not like concat('%,', p.columnB, ',%') and
            lev < 5
     )
select cte.columnA, min(cte.columnB),
       dense_rank() over (order by min(cte.columnB))
from cte
group by cte.columnA;

然后，您可以将其加入以分配组id：

with pairs as (
      select columnA, columnB from myTable
      union -- on purpose to remove duplicates
      select columnB, columnA from myTable
     ),
     cte as (
      select distinct columnA, columnA as columnB,
             convert(varchar(max), ',' + columnA + ',') as visited,
             1 as lev
      from pairs
      union all
      select cte.columnA, p.columnB, concat(visited, p.columnA, ','), lev + 1
      from cte join
           pairs p
           on cte.columnB = p.columnA
      where cte.visited not like concat('%,', p.columnB, ',%') and
            lev < 5
     ),
     groups as (
      select cte.columnA, min(cte.columnB) as min_columnB,
             dense_rank() over (order by min(cte.columnB)) as group_id
      from cte
      group by cte.columnA
     )
select t.*, g.group_id
from mytable t join
     groups g
     on g.columnA = t.columnA;

这是一把小提琴。