我的
MySQL表结构是这样的.
@H_301_2@USER
int id
varchar username
FRIEND_LIST
int user_id
int friend_id
对于每个朋友关系,我在FRIEND_LIST中插入2条记录.
如果用户1是用户2的朋友,则将下一行插入FRIEND_LIST
我想得到特定用户朋友的朋友和朋友.
select应该返回列a,b,c.
@H_301_2@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的朋友和朋友的朋友应该返回：
@H_301_2@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的答案,这是返回我正在寻找的内容的查询.
@H_301_2@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;解决方法
这效率较低但可读：
@H_301_2@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
要么
@H_301_2@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