Sql查询以获取共同的好友

Question

我有两个表，一个叫users(id，名字，姓氏…(第二个叫followers，它包含关于哪个用户在关注其他用户的数据

以下表格示例

userId   followerId
6           10
6           2
6           3
8           1
8           2
8           3

如何获取两个用户之间的共同好友数

预期结果应为

first_user second_user num_of_mutual_friends
83          73               3

Answer 1

WITH firstUser AS (
SELECT followerId
FROM followings
WHERE userId = 6 
)
, secondUser AS (
SELECT followerId
FROM followings
WHERE userId = 8 
)
SELECT COUNT(followerId)
FROM firstUser
INNER JOIN secondUser ON firstUser.followerId = secondUser.followerId

Answer 2

有多种方法可以实现您想要的目标，但这应该能奏效：

select
f.userId as first_user,
s.userId as second_user,
count(*) as num_of_mutual_friends
from
followings f
inner join followings s on s.followerId = f.followerId
and s.userId = 6
where
f.userId = 8
group by
f.userId,
s.userId

你可以在这里查看一个工作示例。

Answer 3

从用户加入以下两个：

select u.*
from users
join followings a on a.followerId = u.id
and a.userid = 6
join followings b on b.followerId = u.id
and b.userid = 8

Answer 4

您也可以使用子查询来完成此操作。因此，如果你想获得id为8的用户和id为6的用户之间的相互关注者数量，你可以使用这个：

解决方案1:

SELECT COUNT(followerId) AS mutual_followers_count FROM followings WHERE userId = 6 AND followerId IN (SELECT followerId FROM followings WHERE userId = 8)

解决方案2:

SELECT COUNT(DISTINCT(followerId)) AS mutual_followers_count FROM followings WHERE followerId in (SELECT followerId FROM followings WHERE userId = 6) AND followerId in (SELECT followerId FROM followings WHERE userId = 8)