mysql - 以单向关系表寻找共同 friend

Question

想要 mysql 查询来查找两个 friend 之间的共同 friend ，但是我以一种方式维持与前用户的友谊。

第一个是用户表

id  name
1   abc
2   xyz
3   pqr

现在第二张 table 是 friend

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

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

Answer 1

已修订

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

此查询应满足规范:

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 here http://sqlfiddle.com/#!2/b23a5/2

下面更详细地解释了我们如何实现这一目标。下面的原始查询假设 friend 表中的一行表示“单向”关系，其中“'abc' ff 'xyz'”并不意味着“'xyz' ff ' abc'”。但 OP 的其他评论暗示情况并非如此。

<小时/>

如果 friend(user_id,friend_id) 存在唯一约束，则获取结果的一种方法是获取每个用户的所有好友，并获取行数那个 friend 。如果计数为 2，则我们知道用户“abc”和“xyz”都会出现特定的 friend_id

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) 进行比较的值，此方法还可以扩展到查找三个或更多用户的共同好友。

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

<小时/>

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

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' 的共同好友。

<小时/>

跟进

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

现在听起来好像您希望该关系表中的一行被视为“双向”关系，而不仅仅是“单向”关系。听起来您想考虑将 friend 关系 ('abc','xyz') 等同于 ('xyz','abc')。

要实现这一点，所需要做的就是让查询创建逆行，这使得查询更容易。我们只需要小心，如果这两个行 ('abc','xyz') 和 ('xyz','abc') 都已经存在，那么当我们反转它们时，我们不会创建它们的重复项。

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

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 表上包含谓词，情况会更简单，但这可能是一个非常大(且昂贵)的行集。