共同朋友的SQL查询 [英] SQL query for mutual friends

问题描述

我的MySQL表结构是这样的.

My MySQL tables structure is like this.

USER
uid

FRIENDS
fuid,fuid2,fapproved

对于每个朋友关系，我在朋友中插入2条记录.如果用户1是用户2的朋友，那么接下来的行将插入到朋友中

For each friend relationship I insert 2 records in FRIENDS. If user 1 is friend of user 2 then the next rows are inserted into FRIENDS

1,2,1

2,1,1

1,3,1

3,1,1

2,3,1

3,2,1

用户ID 3是用户ID 1和用户ID 2的朋友

User id 3 is friend of user id 1 and user id 2

如何在一个SQL查询中获取用户ID 3?

How to get user id 3 in one sql query?

推荐答案

给两个用户@ friend1和@ friend2找到所有是他们共同朋友的用户:

Given two users @friend1 and @friend2 find all the users who are mutual friends of them:

SELECT user.uid
FROM user
WHERE EXISTS(
    SELECT TOP 1 1 
    FROM Friends 
    WHERE Friends.fuid = @friend1 AND Friends.fapproved = 1 
      AND Friends.fuid2 = User.uid
  )
  AND EXISTS(
    SELECT TOP 1 1 
    FROM Friends 
    WHERE Friends.fuid = @friend2 AND Friends.fapproved = 1 
      AND Friends.fuid2 = User.uid
  )

这篇关于共同朋友的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！