SQL获取用户的朋友和朋友的朋友
我的MySQL表结构是这样的。USER
int id
varchar username
FRIEND_LIST
int user_id
int friend_id
对于每个朋友关系，我在FRIEND_LIST中插入2条记录。如果用户1是用户2的朋友，则将下一行插入FRIEND_LIST
1,2
2,1
我想结识特定用户的朋友和朋友的朋友。
选择应返回列a，b，c。
a: user_id
b: friend_id
c: username (username of friend_id )
If 1 is friend of 2 and 3.
2 is friend of 3, 4 and 5
3 is friend of 5,6,7
然后获取1的朋友和朋友的朋友的查询应返回：
1 2 two
1 3 three
2 1 one
2 3 three
2 4 four
2 5 five
3 1 one
3 5 five
3 6 six
3 7 seven
我可以通过一个查询获得这些行吗？
更新答案： 我对DVK的答案做了一些修改，这是返回我所要查找内容的查询。
SELECT friends.user_id, friends.friend_id, username
FROM
FRIEND_LIST friends, USER
WHERE
CAT_USER.id = friends.friend_id
AND
friends.user_id = 1
UNION
SELECT
fof.user_id, fof.friend_id, username
FROM
FRIEND_LIST friends, FRIEND_LIST fof, USER
WHERE
USER.id = fof.friend_id
AND
friends.friend_id = fof.user_id
AND
friends.user_id = 1;
解决方案:
这效率较低，但可读性强：
SELECT friends.user_id, friends.friend_id, username
FROM FRIEND_LIST friends, USER
WHERE USER.id = friends.friend_id
ANDUSER.id = 1
UNION
SELECT USER.user_id, fof.friend_id, username
FROM FRIEND_LIST friends, FRIEND_LIST fof, USER
WHERE USER.id = fof.friend_id
ANDfriends.friend_id = fof.user_id
ANDUSER.id = 1
或者
SELECT user_id, f_fof.friend_id, username
FROM USER, (
SELECT f.user_id, f.friend_id
FROM FRIEND_LIST f
WHEREuser_id = 1
UNION
SELECT f.user_id, fof.friend_id
FROM FRIEND_LIST f, FRIEND_LIST fof
WHEREuser_id = 1
AND f.friend_id = fof.user_id
) as f_fof
WHERE USER.id = f_fof.friend_id
页:
[1]