SQL获取用户的朋友和朋友的朋友 [英] SQL to get friends AND friends of friends of a user
问题描述
我的MySQL表结构是这样的.
My MySQL tables structure is like this.
USER
int id
varchar username
FRIEND_LIST
int user_id
int friend_id
对于每个朋友关系,我在FRIEND_LIST中插入2条记录. 如果用户1是用户2的朋友,则将下一行插入FRIEND_LIST
For each friend relationship I insert 2 records in FRIEND_LIST. If user 1 is friend of user 2 then the next rows are inserted into FRIEND_LIST
1,2
2,1
我想结识特定用户的朋友和朋友.
I want to get the friends and friends of friends of an specific user.
该选择应返回a,b,c列.
The select should return columns 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个朋友的朋友和朋友的朋友的查询应返回:
Then the query to get 1's friends and friends of friends should return:
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
我可以通过一次查询获得这些行吗?
Can I get this rows with a single query?
更新答案::我稍微修改了DVK的答案,这是返回我所要查找内容的查询.
UPDATE ANSWER: I modified DVK's answer a little bit and this is the query that returns what I was looking for.
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;
推荐答案
效率较低,但可读性强:
This is less efficient but readable:
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
OR
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
这篇关于SQL获取用户的朋友和朋友的朋友的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!