如何在mysql中找到像linkedin这样的用户的二级连接？

Question

我需要编写一个查询来获取用户的二级连接。

假设有四个朋友:A，B，C，D

A is friend with B
B is friend with C and D

现在我想找到A，即C和D的二级连接

数据库设计

表:用户

id name
1  A
2  B
3  C
4  D 

表: user_friends

id  friend1  friend2
1    1        2
2    2        3
3    2        4

有人能帮我查询吗？

Answer 1

这样做是可行的：

SELECT f.name
FROM users AS u, user_friends AS a, user_friends AS b, users as f
WHERE u.name='A' AND u.id != f.id AND
  ((u.id = a.friend1 AND a.friend2 = b.friend1 AND b.friend2 = f.id) OR
   (u.id = a.friend1 AND a.friend2 = b.friend2 AND b.friend1 = f.id) OR
   (u.id = a.friend2 AND a.friend1 = b.friend1 AND b.friend2 = f.id) OR
   (u.id = a.friend2 AND a.friend1 = b.friend2 AND b.friend1 = f.id));