如何获得共同朋友的名单

Hi IM trying to find out how i can get mutual friends

你好IM试图找出我如何能够得到共同的朋友

im currently having problems thinking this out.

我目前在思考这个问题时遇到了问题

I have a table called "users" and this is how it looks

我有一个名为“用户”的表,这就是它的外观

id | name
-----------
1   Kenny
2   Jack
3   Jimmy
4   Chris
5   Meg
6   Jake
7   Micheal
8   Dude

I have a table called "friendship" and this is how it looks

我有一张名为“友谊”的桌子,这就是它的样子

user_a | user_b 
----------------
4         1        
7         5
8         1
2         4
2         1
5         2
1         6
1         7

user_a sends user_b a request to become friends and... BAM there are friends. Now if im user 2, and i go to user 1s friend list, i want to see what friends we have in common. What is the correct sql to do that?

user_a向user_b发送成为朋友的请求... BAM有朋友。现在,如果用户2,我去用户1的朋友列表,我想看看我们有什么共同的朋友。这样做的正确sql是什么?

1 个解决方案

#1

Assuming that it is not possible for User 1 to be friends with User 7 twice, (meaning there can not be a row with user_a = 1, user_b=7 and another row user_a = 7, user_b = 1).

假设用户1不可能两次成为用户7的朋友,(意味着不能有user_a = 1的行,user_b = 7而另一行user_a = 7,user_b = 1)。

SELECT IF(user_a = 1 OR user_a = 2, user_b, user_a) friend
FROM friendship
WHERE (user_a = 1 OR user_a = 2) OR (user_b = 1 OR user_b = 2)
GROUP BY 1
HAVING COUNT(*) > 1

1 个解决方案

#1

更多相关文章

随机推荐