The table consists of the columns user1, user2, connectionStrength and example entries:
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 get all users to a certain degree of connection for the selected user, and then draw a connection graph. However, the question is how to select all the records from the table that satisfy the condition. For example, if user A is selected and the degree is set to 2, select the following entries from the example:
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 fact, the database I'm working on has more than 200 M connections, and I am currently using C # and Microsoft SQL Server 2008 for analysis.
- , ( 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 , , - , .
!