MySQL 查询共同好友 [英] MySQL query for mutual friends

问题描述

可能的重复:
MYSQL 选择共同好友

我有一张友谊表，友谊只存储在一行中.所以没有重复的条目.

I have a table for friendship, the friendship is stored only in one line. So there is no duplicate entries.

id  Person1    Person2  status
1         1          2  friend
2         1          3  friend
3         2          3  friend
4         3          4  friend

什么 MySQL 查询(连接、内部连接)将帮助我找到第 1 个人和第 3 个人之间的共同(共同)朋友?此示例中的输入为 {1,3}，输出应为 {2}，因为人员 #2 是机器人 #1 和 #3 的朋友.

What MySQL query (join, inner join) will help me to find common (mutual) friends between person #1 and person #3? The input in this example is {1,3} and the output should be {2} since Person #2 is friend with bot #1 and #3.

推荐答案

嗯，到目前为止，唯一可能有效的查询是 Simon 的……但这确实有点矫枉过正——如此复杂、令人讨厌的查询(2 个子查询有 2 个联合！) 这么简单的事情，你需要悬赏?:-) 如果你有 1000 多个用户，查询会慢得要命——记住，它是二次方的，而且由于子查询中的联合，几乎不会使用任何索引！

Well, the only query that might work up to now is Simon's... but that's real overkill - such a complex nasty query (2 subqueries with 2 unions!) for so simple thing that you need to place a bounty? :-) And if you have like 1000+ users the query will be slow as hell - remeber, it's quadratic, and due to unions in subqueries, hardly any index would be used!

我建议再次重新考虑设计，并允许 2 行重复:

I'd suggest to re-think the design again and allow for 2 duplicate rows for a friendship:

id  Person1    Person2  status
1         1          2  friend
2         2          1  friend
3         1          3  friend
4         3          1  friend

您可能认为这效率低下，但以下简化将允许将查询重写为简单连接:

You might think that's inefficient but following simplification will allow to rewrite the query to simple joins:

select f1.Person2 as common_friend
from friends as f1 join friends as f2
    using (Person2)
where f1.Person1 = '$id1' and f2.Person1 = '$id2' 
    and f1.status = 'friend' and f2.status = 'friend'

这会快得要命！(不要忘记为 Person1,2 添加索引.)我已经建议在其他非常讨厌的数据结构，它加快了查询从永恒到闪电战！

which will be fast as hell! (Don't forget to add indices for Person1,2.) I've advised a similar simplification (rewriting subqueries to joins) in other very nasty data structure and it has speeded up the query from eternity to blitz-instant!

因此，看似很大的开销(一个友谊为 2 行)实际上是一个很大的优化 :-)

So what might have been looking as a big overhead (2 rows for one friendship) is actually a big optimization :-)

此外，它会使诸如查找 X 的所有朋友"之类的查询更加容易.并且不需要花费更多的赏金:-)

Also, it will make queries like "find all friends of X" much more easier. And no more bounties will need to be spent :-)

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