我需要编写一个查询来获取用户的二级连接。
假设有四个朋友: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
有人帮我查询吗?
这将起作用:
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));