如何在SQL中查询朋友的朋友的朋友

Question

>我有以下模式：

我想找到名称为"John"的用户的朋友的朋友的所有朋友。这是我第一次执行这样的嵌套查询，所以我寻求帮助！

例如，我有以下数据：

|---------------------|------------------|
|      User_ID        |     Name         |
|---------------------|------------------|
|          1          |         Jon      |
|---------------------|------------------|
|          2          |         Matt     |
|---------------------|------------------|
|          3          |         Kat      |
|---------------------|------------------|
|          4          |         Sam      |
|---------------------|------------------|
|          5          |         Eli      |
|---------------------|------------------|
|          6          |         Kay      |
|---------------------|------------------|

在另一个表中，我得到了：

|---------------------|------------------|------------------|
|  User_friend_ID     |     user_id      |     friend_id    | 
|---------------------|------------------|------------------|
|          1          |         1        |        2         | 
|---------------------|------------------|------------------|
|          2          |         2        |        3         | 
|---------------------|------------------|------------------|
|          3          |         3        |        4         | 
|---------------------|------------------|------------------|
|          4          |         4        |        2         | 
|---------------------|------------------|------------------|
|          5          |         4        |        3         | 
|---------------------|------------------|------------------|
|          6          |         4        |        5         | 
|---------------------|------------------|------------------|

查询的结果是具有 ID 的用户的名称：

2,3,4,5

Answer 1

@Juan罗

梅罗和@dasblinkenlight已经给出了很好的答案。 如果需要，以下是几个更复杂的选项。 即允许您轻松地在朋友的朋友之间切换为朋友的朋友，而无需重新编码，并排除朋友的朋友的朋友，他们也是朋友的朋友的朋友。

declare @userId long = 1 --whichever user's friends you're looking for
, @degreesOfSeparation = 3 --friend of a friend of a friend
, @name nvarchar(32) = 'John'
; with relationshipCte as 
(
    --get our patient 0 (the user whose friends we're looking for)
    select 0 degreesOfSeperation
    , user_id
    from [user]
    where user_id = @userId
    full outer join 
    --for every friend, pull back that friend's friends
    select degreesOfSeperation + 1
    , friend_id
    from relationshipCte cte
    inner join [Friends] f 
        on f.user_id = cte.user_id
        --and not f.friend_id = @userId --include this if we want to exclude the original user as being a friend of a friend (and similar)
)
select *
from relationshipCte cte
inner join [user] u
on u.user_id = cte.user_id
where cte.degreesOfSeperation = @degreesOfSeperation
and u.Name = @name
order by u.user_id
option (maxrecursion 10) --never go more than 10 relationships away from patient 0 (arguably a reasonable assumption given https://en.wikipedia.org/wiki/Six_degrees_of_separation)

如果您想避免包括任何分离度超过 3 度的朋友(例如，您与吉姆和简是朋友;吉姆和简是彼此的朋友;他们都是朋友，也是朋友的朋友(，请使用以下内容：

declare @userId long = 1 
, @degreesOfSeparation long = 3
, @i long = 1
, @name nvarchar(32) = 'John'
declare @relationships table 
(
    user_id long
    , closestRelation long
)
insert @relationships (user_id, closestRelation) values (@userId, 0) --insert patient 0
while @i <= @degreesOfSeperation
begin
    insert @relationships (user_id, closestRelation) --insert friends
    select f.friend_id, @i
    from @relationships
    inner join Friends f
    on f.user_id = u.user_id
    where not exists (select top 1 1 from @relationships r where r.user_id = f.friend_id) --exclude friends already in our list
   SET @i = @i + 1
end
select *
from @relationships r
inner join [user] u
on u.user_id = r.user_id
where r.closestRelation = @degreesOfSeperation
and u.Name = @name
order by u.user_id

注意：我没有机会运行上述SQL;但希望应该像以前所说的那样工作......

Answer 2

从查询朋友开始 ( f0 (：

select friend.*
from User friend
join Friends f0 on friend.user_id=f0.friend_id
where f0.user_id=my_user_id

通过添加第二个联接将其展开以执行好友的好友f1：

select friend.*
from User friend
join Friends f1 on friend.user_id=f1.friend_id
join Friends f0 on f1.user_id=f0.friend_id
where f0.user_id=my_user_id

将第三个联接添加到f2将为你提供一个朋友的朋友的朋友。您应该能够按照上面的两个示例完成此练习。

Answer 3

这应该有效：

SELECT ff.user_id, uf.name FROM [User] u
INNER JOIN [Friends] f ON u.user_id = f.user_id --= to find his friends
INNER JOIN [Friends] ff ON f.friend_id = ff.user_id --= f.friend_id is his friends
INNER JOIN [User] uf ON ff.friend_id = uf.user_id --= ff.friend_id is friends of his friends
WHERE u.name = 'John'
GROUP BY ff.user_id,uf.name

它选择朋友的朋友的朋友的ID以及他的名字。