如何检索连接的用户，从表中的两个用户之间连接的集群？ [英] How to retrieve a cluster of connected users from table of connections between two users?

问题描述

该表包括对用户的这些连接在一起的。下面是一个假设的例子：

The table consists of pairs of users which are connected together. The following is a hypothetical example:

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

通过随机拿起一个用户从表（用户1或用户2）我想检索到所选择的用户所属的连接的整个集群。例如，如果用户d被选择的查询（或算法）应返回的连接

By randomly picking up a user from the table (user1 or user2) I would like to retrieve the whole cluster of connections to which the selected user belongs. For example if the user d is selected the query (or an algorithm) should return connections

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

有谁知道如何形成一个查询语句或创建一个算法来获取连接集群？

Does anyone know how to form a query statement or create an algorithm to retrieve the connections cluster?

感谢您！

推荐答案

通过使用递归CTE，像这样：

By using a recursive CTE, like so:

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

这篇关于如何检索连接的用户，从表中的两个用户之间连接的集群？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！