我的
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
我想得到特定用户朋友的朋友和朋友.
select应该返回列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,username FROM FRIEND_LIST friends,USER WHERE USER.id = friends.friend_id AND USER.id = 1 UNION SELECT USER.user_id,USER WHERE USER.id = fof.friend_id AND friends.friend_id = fof.user_id AND USER.id = 1
要么
SELECT user_id,f_fof.friend_id,username
FROM USER,(
SELECT f.user_id,f.friend_id
FROM FRIEND_LIST f
WHERE user_id = 1
UNION
SELECT f.user_id,fof.friend_id
FROM FRIEND_LIST f,FRIEND_LIST fof
WHERE user_id = 1
AND f.friend_id = fof.user_id
) as f_fof
WHERE USER.id = f_fof.friend_id
