MySQL查询共同好友

Question

我有一个朋友关系表，朋友关系仅在一行中存储，因此没有重复的条目。这篇文章可能与MYSQL select mutual friends重复。

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

什么MySQL查询语句（join，inner join）可以帮助我找到第1个人和第3个人之间的共同（互相的）朋友？在这个例子中输入是{1,3}，输出应该是{2}，因为第2个人是与第1个人和第3个人都是朋友。

- ilhan

---

你能在sqlfiddle.com上创建一个简单的模式吗？ - Starx

@Starx，http://i.imgur.com/U1wDg.png - ilhan

不，我的意思是一个实时模式，我可以在其中测试我的查询。 - Starx

你是在强制执行 Person1 < Person2 还是这只是巧合？ - Simon

@Simon，不是的。Person1是第一个请求加为好友的人。 - ilhan

Answer 1

好的，到目前为止唯一可能有效的查询是Simon的...但这真的是杀鸡焉用牛刀 - 你需要放置赏金来完成如此复杂的查询（2个带有2个并集的子查询！）仅仅是为了实现这么简单的事情？ :-) 如果你有像1000个以上的用户，这个查询会慢得要命 - 记住，它是二次的，由于子查询中使用了联合，几乎不会使用任何索引！

我建议重新思考设计，并允许“友谊”拥有2个重复行：

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

你可能认为这并不高效，但是通过简化，可以将查询重写为简单的连接：

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添加索引。）我曾在其他非常糟糕的数据结构中建议过类似简化（将子查询重写为联接），并且它加速了查询时间，从漫长的等待变成了即时完成！

所以，原本看起来像是巨大开销的事情（每个友谊需要2行），实际上是一个很大的优化 :-)

此外，这将使得“查找X的所有朋友”之类的查询更加容易。再也不需要花费赏金了 :-)

- Tomas

---

你说得很对。这是一种情况，其中去规范化的好处很可能超过了成本。使用现有数据，您必须像我的答案中的查询一样费尽周折才能测试双向朋友关系。 - Simon

嗯，@Simon。但是这个解决方案并不是任何形式的去规范化！这是表示对称二元关系最自然和本地的方式，不违反任何NF。相反，省略“重复项”的原始解决方案，而不是这个解决方案，应该被视为优化的尝试（这并没有得到回报）。 - Tomas

@Simon，请记住我们处于关系数据库中，我们代表关系，所以你所谓的“双向”关系是某种不自然的、额外的约束条件，在数据库中并没有自然地表示出来，它本身需要“额外的逻辑”——当插入(P1,P2)时，你必须想办法处理(P2,P1)不存在的情况。顺便说一下，与这种自然的关系解决方案相比，你的解决方案是否展示了额外的逻辑呢？ - Tomas

你说得对 - 我一直认为@george的当前模式没有数据异常，尽管很难查询。然后我意识到，在当前模式中，没有任何东西可以防止创建现有关系的反向并导致混乱的删除。我现在看到，当前和您提出的模式在规范化水平上大致相同。 - Simon

四年多过去了，你仍然为我节省了很多时间。谢谢你，真的非常感谢你。 - mithataydogmus

Answer 2

这个查询假设在朋友关系表中没有自我加好友和重复的情况，如果不符合这些条件，则需要进行一些小调整才能使其正常工作。

SELECT fid FROM 
(
    --FIRST PERSON (X) FRIENDLIST
    SELECT 
        (CASE WHEN Person1 = X THEN Person2 ELSE Person1 END) AS fid
    FROM Friendships WHERE (Person1 = X OR Person2 = X) AND status = "friend"
    UNION ALL --DO NOT REMOVE DUPLICATES WITH ALL JOIN
    --SECOND PERSON (Y) FRIENDLIST
    SELECT 
        (CASE WHEN Person1 = Y THEN Person2 ELSE Person1 END) AS fid
    FROM Friendships WHERE (Person1 = Y OR Person2 = Y) AND status = "friend"
) FLIST
GROUP BY fid
HAVING COUNT(*) = 2

- frail

Answer 3

set search_path='tmp';

DROP TABLE friendship CASCADE;
CREATE TABLE friendship
        ( id integer not null PRIMARY KEY
        , person1 integer not null
        , person2 integer not null
        , status varchar
        , CONSTRAINT pk1 UNIQUE (status,person1,person2)
        , CONSTRAINT pk2 UNIQUE (status,person2,person1)
        , CONSTRAINT neq CHECK (person1 <> person2)
        );

INSERT INTO friendship(id,person1,person2,status) VALUES
 (1,1,2,'friend' ) ,(2,1,3,'friend' ) ,(3,2,3,'friend' ) ,(4,3,4,'friend' )
        ;

        -- -----------------------------------------
        -- For implementations that don't have CTEs, 
        -- a view can be used to emulate a CTE.
        -- -----------------------------------------
CREATE VIEW flip AS (
        SELECT person1 AS one
                , person2 AS two
        FROM friendship WHERE status = 'friend'
        UNION
        SELECT person2 AS one
                , person1 AS two
        FROM friendship WHERE status = 'friend'
        );

SELECT DISTINCT
        f1.two AS common
FROM flip f1
JOIN flip f2 ON f1.two = f2.two
WHERE f1.one = 1
AND f2.one = 3
        ;

DROP VIEW flip;

        -- ------------------------------
        -- The same query with a real CTE
        -- ------------------------------
with flip AS (
        SELECT person1 AS one
                , person2 AS two
        FROM friendship WHERE status = 'friend'
        UNION
        SELECT person2 AS one
                , person1 AS two
        FROM friendship WHERE status = 'friend'
        )
SELECT DISTINCT
        f1.two AS common
FROM flip f1
JOIN flip f2 ON f1.two = f2.two
WHERE f1.one = 1
AND f2.one = 3
        ;

结果：

SET
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "friendship_pkey" for table "friendship"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "pk1" for table "friendship"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "pk2" for table "friendship"
CREATE TABLE
INSERT 0 4
CREATE VIEW
 common 
--------
      2
(1 row)

DROP VIEW
 common 
--------
      2
(1 row)

- wildplasser

Answer 4

另外一个答案。

select 
    (case when f1.person1 = 1 then f1.person2 else f1.person1 end) as fid
from friends f1
where f1.person1 = 1 or f1.person2 = 1
and f1.status = 'friend'

intersect

select 
    (case when f1.person1 = 3 then f1.person2 else f1.person1 end) as fid
from friends f1
where f1.person1 = 3 or f1.person2 = 3
and f1.status = 'friend'

- SQLCurious

Answer 5

我认为这可以通过以下方式轻松实现

SELECT * FROM friends

WHERE
     (Person1 = '1' or Person2 = '1') && 
     (Person1 = '2' or Person2 = '2') &&
     status = 'friend'

假设你正在尝试寻找人1和人2之间的共同点

- Starx

---

硬编码到示例。 - Michael Durrant

@Starx，它可以找到#1和#2的朋友，但不列出共同的朋友。 - ilhan

@MichaelDurrant，有时候SO太过了，有时候是硬编码，有时候与情况无关。Arrrrrrrr - Starx

Answer 6

我问过是否较低编号的用户总是Person1，但最终编写的查询并不关心这一点。

set @firstParty = 1, @secondParty = 3

select friends_of_first.friend
from (
    select Person2 as friend from friends where Person1 = @firstParty
    union 
    select Person1 as friend from friends where Person2 = @firstParty
    ) as friends_of_first
join (
    select Person2 as friend from friends where Person1 = @secondParty
    union 
    select Person1 as friend from friends where Person2 = @secondParty
    ) as friends_of_second
on friends_of_first.friend = friends_of_second.friend

查找用户朋友的子查询可以替换为 @Nirmal-thInk beYond 使用的一个：

select case when f1.person1 = @firstParty then  f1.person2 else f1.person1 end 
from friend f1 where f1.person1 = @firstParty or f1.person2 = @firstParty

我很想知道哪个替代方案表现更好。

- Simon

Answer 7

如果已经有其他回复或评论提到了这个建议，那么请原谅我的重复。不过，我的建议是：

select Person2 mutual_friend from 
  (select Person1, Person2 from friends 
      where Person1 in (1,3) union 
   select Person2, Person1 from friends 
      where Person2 in (1,3)
  ) t 
  group by Person2 having count(*) > 1;

- Kolbe

Answer 8

内部查询仅针对第一个人获取 FRIEND IDs，并将其标准化为单个列 "FriendID"。如果找到的记录在第一个位置具有人员 ID = 1，则获取第二个... 如果人员 ID = 1 在第二个位置，则获取第一个。

完成上述操作后，我们知道了第一人的朋友单列表... 完成。现在，再次加入交友表，但仅限于那些首先被确认为是第一人之一的朋友... 一旦确认了，再确保第二张表上的另一个人是你要查找共同点的第三个人。

确保 person1 上的索引和 person2 上的索引以利用 OR 条件。

select
      JustPerson1Friends.FriendID
   from
      ( select
              if( f.Person1 = 1, f.Person2, f.Person1 ) as FriendID
           from
              Friendships f
           where
                   (    f.Person1 = 1
                     OR f.Person2 = 1 )
               AND f.status = "friend" ) JustPerson1Friends
      JOIN Friendships f2
         on  (   JustPerson1Friends.FriendID = f2.Person1
              OR JustPerson1Friends.FriendID = f2.Person2 )
         AND f2.status = "friend"
         AND ( f2.Person1 = 3 OR f2.person2 = 3 )

另一种选项是将人员“3”预先标记为结果集中的常见人员，这样我们就不需要在后面明确限定3。此外，通过使用MySQL变量，可以轻松地编写脚本并将其实现为参数。在内部查询之后，执行DOUBLE左连接到友谊关系，以显式测试一个人可能被找到在X / Y或Y / X组合中的两个组合。因此，最终的where子句只是说只要在任何LEFT-JOIN条件中找到记录，它就是共同的朋友，并包含在结果集中。

select
      JustPerson1Friends.FriendID
   from
      ( select
              @WantPerson2 as FindInCommonWith,
              if( f.Person1 = @WantPerson1, f.Person2, f.Person1 ) as FriendID
           from
              ( select @WantPerson1 := 1,
                       @WantPerson2 := 3 ) sqlvars
              Friendships f,
              (
           where
                   (    f.Person1 = @WantPerson1
                     OR f.Person2 = @WantPerson2 )
               AND f.status = "friend" ) JustPerson1Friends

      LEFT JOIN Friendships f2
         on JustPerson1Friends.FindInCommonWith = f2.Person1
         AND JustPerson1Friends.FriendID = f2.Person2
         AND f2.status = "friend"

      LEFT JOIN Friendships f3
         on JustPerson1Friends.FindInCommonWith = f2.Person2
         AND JustPerson1Friends.FriendID = f2.Person1
         AND f2.status = "friend"
   where
         f2.Person1 > 0
      OR f3.Person1 > 0

- DRapp

Answer 9

这个查询返回结果为'22'，因为它在1和3中都是共同的。 你可能需要过滤掉不同的PERSON1/PERSON2。 如果我能优化这个查询，我会更新它。

SELECT DISTINCT (REPLACE(TRANSLATE((WM_CONCAT(DISTINCT F.PERSON1) || ',' ||
                                           WM_CONCAT(DISTINCT F.PERSON2)),
                                           '1,3',
                                           ' '),
                                 ' ',
                                 '')) AS COMMON_FRIEND
          FROM FRIENDSHIP F
         WHERE UPPER(F.STATUS) = 'FRIEND'
         AND ((SELECT DISTINCT WM_CONCAT(F1.PERSON1)
                   FROM FRIENDSHIP F1
                  WHERE F1.PERSON2 = '3') LIKE ('%' || F.PERSON1 || '%') OR
               (SELECT DISTINCT WM_CONCAT(F1.PERSON2)
                   FROM FRIENDSHIP F1
                  WHERE F1.PERSON1 = '3') LIKE ('%' || F.PERSON2 || '%'))
           AND ((SELECT DISTINCT WM_CONCAT(F1.PERSON1)
                   FROM FRIENDSHIP F1
                  WHERE F1.PERSON2 = '1') LIKE ('%' || F.PERSON1 || '%') OR
               (SELECT DISTINCT WM_CONCAT(F1.PERSON2)
                   FROM FRIENDSHIP F1
                  WHERE F1.PERSON1 = '1') LIKE ('%' || F.PERSON2 || '%'))
           AND NOT ((F.PERSON1 = '1' AND F.PERSON2 = '3') OR
                (F.PERSON1 = '3' AND F.PERSON2 = '1'))

- Rohan

Answer 10

这应该可以回答您当前的问题，尽管我建议不要像这样做。在这种情况下，我总是选择存储两个关系的副本，每个方向一个。

SELECT IF(f1.person1 IN ($id1, $id3), f1.person2, f1.person1) AS mutual_friend
FROM friends f1
INNER JOIN friends f2
    ON (f1.person1 = $id1 AND f2.person1 = $id3 AND f1.person2 = f2.person2)
    OR (f1.person1 = $id1 AND f2.person2 = $id3 AND f1.person2 = f2.person1)
    OR (f1.person2 = $id1 AND f2.person1 = $id3 AND f1.person1 = f2.person2)
    OR (f1.person2 = $id1 AND f2.person2 = $id3 AND f1.person1 = f2.person1)
WHERE f1.status = 'friend' AND f2.status = 'friend'

- user1191247

---

他的一个列表列出了两个人的所有朋友。它不会列出共同的朋友。顺便说一句，你也只有一个参数。它应该是两个参数。 - ilhan