sql - 如何从两个用户之间的连接表中检索已连接用户的集群？

表由连接在一起的用户对组成。下面是一个假设的例子：

user1, user2
a, b
a, c
c, a
c, d
d, e
a, e
j, n
g, n
f, n

通过从表中随机选取一个用户（user1或user2），我希望检索所选用户所属的整个连接集群例如，如果选择了用户d，则查询（或算法）应返回连接

a, b
a, c
c, a
c, d
d, e
a, e

有人知道如何形成查询语句或创建算法来检索连接群集吗？
谢谢您！

最佳答案

通过使用递归CTE，如下所示：

with combinedusers as
(select user1 userX, user2 userY from usertable union
 select user2 userX, user1 userY from usertable)
, relatedusers as
(select c.userX,
        c.userY,
        convert(varchar(max),'\' + c.userX + '\' + c.userY + '\') hierarchy
        from combinedusers c where userX = 'd'
 union all
 select c.userX,
        c.userY,
        convert(varchar(max),r.hierarchy  + c.userY + '\') hierarchy
        from combinedusers c
        join relatedusers r
        on c.userX = r.userY and charindex('\' + c.userY + '\',r.hierarchy)=0)
select * from
(select userX, userY from relatedusers union
 select userY, userX from relatedusers) r where userX < userY