SQL获取用户朋友和朋友的朋友

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!