SQL查询查找给定的人,朋友对表中的共同朋友数 [英] SQL Query to find number of mutual friends given a table of person, friend pair
问题描述
我搜索了其他问题,我能够解决部分我想要的东西,但是我无法从中得到进一步的帮助.
I have searched for other questions and I was able to solve part of what I wanted but I couldn't get any further from that.
我在Friends表中有一个包含两列(用户,friend)的表. 下表中将每个用户及其朋友指定为波纹管.
I have a table with two columns(user, friend) in Friends table. Each user and his/her friend is specified in the table as bellow.
User | Friend
1 2
1 6
2 1
2 3
2 6
注意:对于每对(用户,朋友),都有一行(朋友,用户) 例如:1,2有2,1,因为用户2有朋友1
Note: For every (User, friend) pair there is a row (Friend, User) Eg: 1,2 has 2,1 because User 2 has friend 1
到目前为止,我到达了以下查询,该查询给出了我们指定的配对的共同朋友的数量:
So far, I have arrived at the below query which gives the count of mutual friends for the pair we specify:
select DISTINCT f1.user1 'User', f2.user1 'Friend', COUNT(DISTINCT f1.user2) 'Mutual friends'
from Friends p
inner join Friends f1 on f1.user2 = p.user1
inner join Friends f2 on f2.user2 = p.user1
where f1.user1 = 2 and f2.user1 = 3 and f1.user2 = f2.user2
group by f1.user1, f2.user1;
我现在的输出:
User |Friend |Mutual Friends
1 2 1
我想查找整个表格中每对的共同朋友的数量:
I want to find the count of mutual friends of each of pairs throughout the table:
User | Friend | Mutual Friends
1 2 1
1 6 0
2 1 1
2 3 0
2 6 0
如何找到所有用户,朋友对的共同朋友的数量?
How do I find the number of mutual friends for all user,friend pairs?
推荐答案
您可以使用自联接:
select f1.user as user1, f2.user as user2, count(*) as num_in_common
from friends f1 join
friends f2
on f1.friend = f2.friend
group by f1.user, f2.user;
如果需要特定用户对的信息,可以添加where
子句.
You can add a where
clause if you want this information for a particular pair of users.
这篇关于SQL查询查找给定的人,朋友对表中的共同朋友数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!