代码之家 › 专栏 › 技术社区 › Enrique

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

mysql sql

Enrique · 技术社区 · 16 年前

我的mysql表结构是这样的。

USER
int id
varchar username

FRIEND_LIST
int user_id
int friend_id

对于每个朋友关系,我在朋友列表中插入2条记录。如果用户1是用户2的朋友,则下一行将插入朋友列表

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;

2 回复 | 直到 16 年前

DVK 16 年前

这效率较低,但可读性较差:

SELECT friends.user_id, friends.friend_id, username 
FROM FRIEND_LIST friends, USER
WHERE USER.id           = friends.friend_id
 AND  USER.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
 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

a1ex07 16 年前

SELECT f1.user_id, f1.friend_id FROM
friends_info f1
WHERE f1.user_id = 1 OR
f1.user_id IN
(
 select f2.friend_id
 from friends_info f2
 where f2.user_id = 1
)
 ORDER BY user_id

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前