查询 MySQL 中的好友及共同好友数量

Question

我已经查看了这些问题，但没有找到完全符合我需求的问题，也无法自己解决。

我有两个表，一个是用户表，一个是好友链接表。用户表是所有用户的表格：

    +---------+------------+---------+---------------+
    | user_id | first_name | surname |     email     |
    +---------+------------+---------+---------------+
          1         joe       bloggs    [e-mail]
          2         bill      bloggs    [e-mail]
          3         john      bloggs    [e-mail]
          4         karl      bloggs    [e-mail]

我的朋友链接表显示了用户之间所有的关联，例如：

    +--------=+---------+-----------+--------+
    | link_id | user_id | friend_id | status |
    +---------+---------+-----------+--------+
       1         1          3           a
       2         3          1           a
       3         4          3           a
       4         3          4           a
       5         2          3           a
       6         3          2           a

作为说明，状态列中的a表示已批准，还可以有r（请求）和d（拒绝）。

我想要的是一个查询，如果用户进行搜索，它将返回一个用户列表，他们目前还不是朋友，并且每个用户与他们有多少共同的朋友。

我已经成功地得到了一个查询，用于获取所有当前不是他们朋友的用户。所以如果进行搜索的用户具有用户ID 1：

SELECT u.user_id,u.first_name,u.surname
FROM users u
    LEFT JOIN friend_links fl
        ON u.user_id = fl.user_id AND 1 IN (fl.friend_id)
WHERE fl.friend_id IS NULL
AND u.user_id != 1
AND surname LIKE 'bloggs'

那么，我如何统计每个返回用户的共同好友数量呢？

编辑：

由于我认为我的问题不是特别清楚，因此进行编辑。

我目前拥有的查询将产生以下一组结果：

    +---------+------------+---------+
    | user_id | first_name | surname |
    +---------+------------+---------+
          2         bill      bloggs
          4         karl      bloggs

这些用户与“Bloggs”姓氏匹配，目前不是Joe Bloggs（用户ID 1）的好友。

然后我想知道这些用户中每个人与搜索者有多少共同好友，以便返回以下结果：

    +---------+------------+---------+--------+
    | user_id | first_name | surname | mutual |
    +---------+------------+---------+--------+
          2         bill      bloggs     1
          4         karl      bloggs     1

每个返回的用户都有一个共同的好友，因为Joe Bloggs（用户ID 1）与John Bloggs是朋友，而John Bloggs又与两个返回的用户都是朋友。

希望这样更清楚了。

谢谢。

- Sean

Answer 1

可以通过在friend_links表上使用friend_id字段自身连接来找到共同的好友，如下所示：

SELECT *
FROM friend_links f1 INNER JOIN friend_links f2
  ON f1.friend_id = f2.friend_id
WHERE f1.user_id = $person1
  AND f2.user_id = $person2

请记住，这种做法在最坏情况下本质上是将 friend_links 表中的行数平方，并且一旦您拥有数量不少的行，它很容易使您的服务器崩溃。更好的选择是为每个用户使用 2 个子查询，然后连接这些结果。

SELECT *
FROM (
  SELECT *
  FROM friend_links
  WHERE user_id = $person1
) p1 INNER JOIN (
  SELECT *
  FROM friend_links
  WHERE user_id = $person1
) p2
  ON p1.friend_id = p2.friend_id

此外，您可以通过删除代理键“link_id”，并将（user_id，friend_id）设置为主键来简化friend_links表，因为它们必须是唯一的。请注意保留HTML标记。

---

编辑：

如果可能的话，如何将此应用于搜索尚未成为朋友的用户的原始查询？我想在单个查询中执行两个操作

SELECT f2.user_id, COUNT(*) 'friends_in_common'
FROM friend_links f1 LEFT JOIN friend_links f2
  ON f1.friend_id = f2.friend_id
WHERE f1.user_id = $person
GROUP BY f2.user_id
ORDER BY friends_in_common DESC
LIMIT $number

我认为可以把user_id的限制条件从WHERE子句移动到JOIN条件中，这样可以减少自连接创建的数据集的大小，并且避免使用像第二个例子中那样的子查询。

- Sammitch

---

如果要应用于搜索尚未成为好友的用户的原始查询，如果可能的话，我想在单个查询中完成这两个操作。我还想返回一些共同的朋友数量，例如查询将返回用户的名称和该人拥有的共同朋友数量。另外感谢您提供简化friend_links表格的提示，我会采纳的。 - Sean

经过一些测试，这似乎完美地运作了，非常感谢你，我的朋友！ - Sean

当我将此应用于我的实际数据时，我发现它只会返回有共同好友的人，如果他们没有共同好友，则该人不在结果集中，这不是我想要的。 - Sean

将 INNER JOIN 改为 LEFT JOIN。 - Sammitch

在你上一个回答中提到的查询语句中没有包含内连接。我想我可能对你的意思感到困惑了？ - Sean

此答案无效，因为它还会返回已经与 $person 是朋友的用户。 - Shu

Answer 2

这个查询会列出所有不与用户1为好友且姓氏匹配 '%bloggs%' 的人：

SELECT
  users.user_id,
  users.first_name,
  users.surname,
  Sum(IF(users.user_id = friend_links_1.friend_id, 1, 0)) As mutual
FROM
  users inner join
    (friend_links INNER JOIN friend_links friend_links_1
     ON friend_links.friend_id = friend_links_1.user_id)
  ON friend_links.user_id=1 AND users.user_id<>1
WHERE
  users.surname LIKE '%bloggs%'
GROUP BY
  users.user_id, users.first_name, users.surname
HAVING
  Sum(IF(users.user_id = friend_links.friend_id, 1, 0))=0

只需在ON子句中更改用户ID，在WHERE子句中更改姓氏。我认为现在应该可以正常工作了！

- fthiella

---

@fthiella 我该如何筛选那些已经是我的好友的朋友？ - Basit

Answer 3

如果A是B的朋友，那么B也是A的朋友？使用一个链接而不是两个链接（以及两行friends_links）会更好吗？然后您必须使用两个状态列status1和status2，只有当status1 = status2 =“a”时，A才是B的朋友。

有许多显示共同朋友的方法，例如：

SELECT friend_id
FROM friend_links
WHERE friend_links.user_id = $user1 or friend_links.user_id = $user2
  AND NOT (friend_links.friend_id = $user1 or friend_links.friend_id = $user2)
GROUP BY friend_id
HAVING Count(*)>1

这个查询显示每个用户和不是他/她朋友的人：

SELECT
  users.user_id,
  users.first_name,
  users_1.user_id,
  users_1.first_name
FROM
  users INNER JOIN users users_1 ON users.user_id <> users_1.user_id
WHERE
  NOT EXISTS (SELECT *
              FROM friend_links
              WHERE
                friend_links.user_id = users.user_id
                AND friend_links.friend_id = users_1.user_id)

我唯一没有检查的是友谊状态，但添加这个检查很容易。

我仍在努力，但将这两个查询结合起来并不容易。所以这不是一个确切的答案，我只是展示了一些尝试过的想法。

但你需要什么？一个查询返回每个用户和任何不是他/她朋友的人以及共同朋友的数量，还是已经给出了user_id？

用一些代码回答你的问题不是问题...但必须有一种好的方法只使用SQL！ :)

编辑：

我仍在思考是否有更好的解决方案，特别是下一个查询可能会非常慢，但看起来这可能有效：

SELECT
  users_1.user_id,
  users_2.user_id,
  Sum(IF(users_1.user_id = friend_links.user_id AND users_2.user_id = friend_links_1.friend_id, 1, 0)) As CommonFriend
FROM
  users users_1 INNER JOIN users users_2
    ON users_1.user_id <> users_2.user_id,
  (friend_links INNER JOIN friend_links friend_links_1
    ON friend_links.friend_id = friend_links_1.user_id)
GROUP BY
  users_1.user_id,
  users_2.user_id
HAVING
  Sum(IF(users_1.user_id = friend_links.user_id AND users_2.user_id = friend_links.friend_id, 1, 0))=0

（和之前一样，我没有检查好友关系状态）

如果有给定用户，你可以使用 WHERE users_1.user_id=$user1，但最好只留下一个用户表，并使用该用户筛选下一个 INNER JOIN。

- fthiella

---

我最初只有朋友A和朋友B的单条记录，但实际上使用很多查询可以更容易且更快地使用2个记录。例如，仅查询好友时，能够查询单个列而不必查询两个列速度更快。我的理想查询应该是一个搜索查询，返回当前用户还没有交友的所有用户以及每个返回用户的共同好友数量。 - Sean

你试过我的修改后的答案了吗？如果那是你需要的，它可以再优化一下！ - fthiella

我尝试了一下，它确实可以工作，但它没有返回我想要的东西。很抱歉没有表达清楚，这很难解释。请看我的编辑，希望这能稍微澄清一些事情。谢谢你的时间。 - Sean

我认为现在很清楚了...这简化了一些事情...我会发布一个新答案。 - fthiella