选择第 n 级连接内的所有用户
该表由列 user1、user2、connectionStrength 组成，示例记录如下：
A,B,0.2
A,C,0.5
A,G,0.1
B,C,0.8
W,Q,0.4
G,Q,0.5
我想检索所选用户的定义连接程度内的所有用户，然后绘制连接图。然而问题是如何从表中选择满足条件的所有记录。例如，如果选择用户 A 并将程度设置为 2，则应选择示例中的以下记录：
A,B,0.2
A,C,0.5
A,G,0.1
B,C,0.8
G,Q,0.5
上面的示例是假设的。实际上，我工作的数据库中有超过200M的连接，目前我使用C#和Microsoft SQL Server 2008进行分析。
有谁知道如何编写一个函数（string GetQuery（string selectedUser，int DegreeOfConnection））来组成一个查询，该查询返回满足条件（所选用户的连接程度）的所有记录？
编辑 #1
我尝试通过以下方式执行递归查询来获取连接：
WITH user_connections(user1, user2, link_strength, Level)
AS
(
SELECT user1, user2, link_strength, 0 AS Level FROM [dbo].[monthly_connections] AS mc WHERE user1 = '1ADF1126F26B4AD4441A3C552FCE04A4F7A79760'
UNION ALL
SELECT mc.user1, mc.user2, mc.link_strength, Level + 1 FROM [dbo].[monthly_connections] AS mc INNER JOIN user_connections AS uc ON uc.user2 = mc.user1
)
SELECT user1, user2, link_strength FROM user_connections OPTION(MAXRECURSION 1)
到目前为止，查询已经执行了 40 多分钟，所以如果有人可以检查语句是否是，我将非常感激组成正确。
谢谢你！
The table consists of columns user1, user2, connectionStrength and the example records are the following:
A,B,0.2
A,C,0.5
A,G,0.1
B,C,0.8
W,Q,0.4
G,Q,0.5
I would like to retrieve all users within the defined degree of connection for the selected user and then draw a graph of connections. The question however is how to select all the records from the table that fulfill the condition. For example if user A is selected and the degree is set to 2 the following records from the example should be selected:
A,B,0.2
A,C,0.5
A,G,0.1
B,C,0.8
G,Q,0.5
The example above is hypothetical. In reality there are more than 200M connections in the database I work on and currently I use C# and Microsoft SQL Server 2008 for the analysis.
Does anyone know how to write a function (string GetQuery(string selectedUser, int degreeOfConnection)) to compose a query which returns all the records that fulfill the condition (degree of connection for the selected user)?
Edit #1
I tried to get connections by executing recursive query the following way:
WITH user_connections(user1, user2, link_strength, Level)
AS
(
SELECT user1, user2, link_strength, 0 AS Level FROM [dbo].[monthly_connections] AS mc WHERE user1 = '1ADF1126F26B4AD4441A3C552FCE04A4F7A79760'
UNION ALL
SELECT mc.user1, mc.user2, mc.link_strength, Level + 1 FROM [dbo].[monthly_connections] AS mc INNER JOIN user_connections AS uc ON uc.user2 = mc.user1
)
SELECT user1, user2, link_strength FROM user_connections OPTION(MAXRECURSION 1)
The query has been executing for more than 40 minutes so far so I would be very thankful if anyone could just check if the statement is composed correctly.
Thank you!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SQL 2005 及更高版本允许您使用公用表表达式创建递归查询。文档中的示例应该足以帮助您入门。
SQL 2005 and up allows you to create recursive queries using Common Table Expressions. The samples in the documentation should be enough to get you started.
快速实现搜索确实存在问题，因为联系人数量随着联系程度呈指数级增长。我会尝试中间相遇算法。从两个用户中找到 n/2 度的联系人，然后检查这两组是否有共同点。
如果您经常需要这些查询，您可能会考虑不在数据库中运行它们，而是将连接加载到 LookUp 中并在 C# 中运行查询。按受欢迎程度对查找中的价值用户进行排序也可能会提高性能。因为这种联系更有可能是通过受欢迎的人发生的。
It's really problematic to implement that search quickly since the number of contacts grows exponentially with the degree of connection. I'd try a meet in the middle algorithm. From both users find contacts of the n/2 degree and then check if these two sets have somebody in common.
If you need these queries often you might consider not running them in the database, but to load the connections into a LookUp and run the queries in C#. And ordering the value users in the Lookup by popularity might improve performance too. Since the connection is more likely to occur via a popular person.