以一种方式找到共同的朋友关系表 [英] Finding mutual friend in one way relationship table

问题描述

想要通过 mysql 查询找到两个朋友之间的共同朋友，但是我以一种方式维护用户的友谊关系.

want mysql query for finding mutual friend between two friend but I am maintain the friendship of user in one way relationship for ex.

首先是用户表

id  name
1   abc
2   xyz
3   pqr

现在第二张桌子是朋友

id user_id friend_id
1   1      2
2   1      3
3   2      3

现在我可以说 abc(id=1) 是 xyz(id=2) 的朋友，现在类似于 xyz 是 abc 的朋友，但现在我想在 abc(id=1) 和 xyz 之间找到共同的朋友(id=2) 那是 pqr 所以我想要 mysql 查询.

Now here i can say that abc(id=1) is friend of xyz(id=2) now similar way the xyz is friend of abc but now i want to find mutual friend between abc(id=1) and xyz(id=2) that is pqr so I want mysql query for that.

推荐答案

REVISED

此查询将朋友表中一行的单向"关系视为双向"关系.也就是说，它会认为朋友关系:('abc','xyz') 等价于逆关系:('xyz','abc').(注意:我们不能保证两行都不会出现在表中，所以我们需要注意这一点.UNION 运算符可以方便地为我们消除重复.)

This query will consider the "one way" relationship of a row in the friend table to be a "two way" relationship. That is, it will consider a friend relationship: ('abc','xyz') to be equivalent to the inverse relationship: ('xyz','abc'). (NOTE: we don't have any guarantee that both rows won't appear in the table, so we need to be careful about that. The UNION operator conveniently eliminates duplicates for us.)

此查询应满足规范:

SELECT mf.id
     , mf.name
  FROM (
         SELECT fr.user_id AS user_id
              , fr.friend_id AS friend_id
           FROM friend fr
           JOIN users fru
             ON fru.id = fr.user_id
          WHERE fru.name IN ('abc','xyz')
          UNION
         SELECT fl.friend_id AS user_id
              , fl.user_id AS friend_id
           FROM friend fl
           JOIN users flf
             ON flf.id = fl.friend_id
          WHERE flf.user IN ('abc','xyz')
       ) f
  JOIN users mf
    ON mf.id = f.friend_id
 GROUP BY mf.id, mf.name
HAVING COUNT(1) = 2
 ORDER BY mf.id, mf.name

这里的 SQL Fiddle http://sqlfiddle.com/#!2/b23a5/2

SQL Fiddle here http://sqlfiddle.com/#!2/b23a5/2

下面给出了我们如何达到这一点的更详细的解释.下面的原始查询假定朋友表中的一行表示单向"关系，因为'abc' ff 'xyz'" 并不意味着'xyz' ff 'abc'".但来自 OP 的其他评论暗示情况并非如此.

A more detailed explanation of how we arrive at this is given below. The original queries below assumed that a row in the friend table represented a "one way" relationship, in that "'abc' ff 'xyz'" did not imply "'xyz' ff 'abc'". But additional comments from the OP hinted that this was not the case.

如果对 friend(user_id,friend_id) 有唯一约束，那么获​​取结果的一种方法是获取每个用户的所有好友，并获取每个用户的行数那个朋友.如果计数为 2，则我们知道用户 'abc' 和 'xyz' 都出现了特定的 friend_id

If there is a unique constraint on friend(user_id,friend_id), then one way to get the result would be to get all of the friends of each user, and get a count of rows for that friend. If the count is 2, then we know a particular friend_id appears for both user 'abc' and for 'xyz'

SELECT mf.id
     , mf.name
  FROM friend f
  JOIN users uu
    ON uu.id = f.user_id
  JOIN users mf
    ON mf.id = f.friend_id
 WHERE uu.name IN ('abc','xyz')
 GROUP BY mf.id, mf.name
HAVING COUNT(1) = 2
 ORDER BY mf.id, mf.name

(这种方法也可以扩展到查找三个或更多用户的共同朋友，方法是在 IN 列表中包含更多用户，并更改我们与 COUNT(1) 进行比较的值.

(This approach can also be extended to find a mutual friend of three or more users, by including more users in the IN list, and changing the value we compare the COUNT(1) to.

这不是将返回指定结果集的唯一查询；还有其他方法可以获得它.

This isn't the only query that will return the specified resultset; there are other ways to get it as well.

获得等效结果的另一种方法:

Another way to get an equivalent result:

SELECT u.id
     , u.name
  FROM ( SELECT f1.friend_id
           FROM friend f1
           JOIN users u1
             ON u1.id = f1.user_id
          WHERE u1.name = 'abc'
       ) t1
  JOIN ( SELECT f2.friend_id
           FROM friend f2
           JOIN users u2
             ON u2.id = f2.user_id
          WHERE u2.name = 'xyz'
       ) t2
    ON t2.friend_id = t1.friend_id
  JOIN users u
    ON u.id = t1.friend_id
 ORDER BY u.id, u.name

<小时>

注意事项

这些查询不检查用户abc"是否是xyz"(WHERE 子句中指定的两个用户名)的朋友.它只是找到'abc'和'xyz'的共同朋友.

These queries do not check whether user 'abc' is a friend of 'xyz' (the two user names specified in the WHERE clause). It is only finding the common friend of both 'abc' and 'xyz'.

跟进

以上查询满足指定要求，以及问题中提供的所有示例和测试用例.

The queries above satisfy the specified requirements, and all the examples and test cases provided in the question.

现在听起来好像您希望将该关系表中的一行视为双向"关系，而不仅仅是单向"关系.听起来您想要考虑相当于 ('xyz','abc') 的朋友关系 ('abc','xyz').

Now it sounds as if you want a row in that relationship table to be considered a "two way" relationship rather than just a "one way" relationship. It sounds like you want to want to consider the friend relationship ('abc','xyz') equivalent to ('xyz','abc').

要做到这一点，需要做的就是让查询创建反向行，这样查询起来就更容易了.我们只需要注意，如果这两个行 ('abc','xyz') 和 ('xyz','abc') 已经存在，我们在反转它们时不会创建它们的重复项.

To get that, then all that needs to be done is to have the query create the inverse rows,, and that makes it easier to query. We just need to be careful that if both those rows ('abc','xyz') and ('xyz','abc') already exist, that we don't create duplicates of them when we invert them.

要创建反向行，我们可以使用这样的查询.(当我们没有 JOIN 到 users 表，我们只使用 id 值时，看这个更简单:

To create the inverse rows, we can use a query like this. (It's simpler to look at this when we don't have the JOIN to the users table, and we use just the id value:

SELECT fr.user_id
     , fr.friend_id 
  FROM friend fr
 WHERE fr.user_id IN (1,2)
 UNION
SELECT fl.friend_id AS user_id
     , fl.user_id AS friend_id
  FROM friend fl
 WHERE fl.friend_id IN (1,2)

如果我们不在 user_id 和friend_id 表中包含谓词会更简单，但这可能是一个非常大(且昂贵)的行集来实现.

It's simpler if we don't include the predicates on the user_id and friend_id table, but that could be a very large (and expensive) rowset to materialize.

这篇关于以一种方式找到共同的朋友关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！