在(1、2)(2、1)友谊场景中，如何获得共同的朋友? [英] What are ways to get mutual friends in a (1, 2) (2, 1) friendship scenario?

问题描述

我正在开发一个网站，当一个人将另一个人添加为朋友时，将创建2个条目.假设uid 1将uid 2添加为朋友，则以下行在MySQL中创建.

I'm developing a website wherein when a person adds another person as a friend, 2 entries are created. Let's say uid 1 adds uid 2 as a friend, the following rows are created in MySQL.

activity_id   uid1 uid2
     1         1     2
     2         2     1

uid 2与uid 3成为朋友:

activity_id   uid1 uid2
     1         1     2
     2         2     1
     3         2     3
     4         3     2

通过什么方法可以在uid 1和uid 2之间获得共同的朋友?我正在使用没有PDO经验的php和MySQL(目前).

What are the ways to get the mutual friends between uid 1 and uid 2? I'm using php and MySQL with no PDO experience (for now).

好的，所以我决定对表进行组织/标准化.该关系现在仅生成1行.制成表格:

Okay, so I decided to organize/normalize the tables. The relationship only generates 1 row now. which makes the table:

activity_id   uid1 uid2
     1         1     2
     2         2     3

推荐答案

从您的

Looking at the table format from your other question, this should do what you want;

SELECT name FROM users u
JOIN friends f1
  ON u.uid = f1.uid OR u.uid = f1.fid
JOIN friends f2
  ON u.uid = f2.uid OR u.uid = f2.fid
WHERE (f1.uid=1 OR f1.fid=1) AND (f2.uid=3 OR f2.fid=3) 
  AND u.uid<>1 AND u.uid<>3;

演示此处.

这篇关于在(1、2)(2、1)友谊场景中，如何获得共同的朋友?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！