根据以下样本数据,假设Julie(1)有朋友Adam,David,John(2,3,4).
亚当(2)有朋友朱莉,大卫,约翰(1,4).
亚当(2)有朋友朱莉,大卫,约翰(1,4).
ID Name 1 Julie 2 Adam 3 David 4 John 5 Sam
这使得一个表中的自连接和多对多关系成为可能.
除了上述问题之外,Julie(1)还说Sam(5)是朋友,从技术上和实际上讲,Sam(5)现在也是Julie(1)的朋友.这使得事情变得更加复杂,因为这种关系是双向的.
所以我想知道：
>我如何设计数据库？
>如何进行将返回每个用户的所有朋友的查询？
谢谢！
解决方法
示例数据：
PEOPLE PERS_ID PERS_NAME 1 Julie 2 Adam 3 David 4 John 5 Sam FRIENDSHIPS PERS_ID FRIEND_ID 1 2 1 3 1 4 2 3 2 4
查询：
select people.pers_id as person,people.pers_name as person_name,peoplef.pers_id as friend_id,peoplef.pers_name as friend_name
from people
join friendships
on people.pers_id = friendships.pers_id
or people.pers_id = friendships.friend_id
join people peoplef
on (peoplef.pers_id = friendships.pers_id and
peoplef.pers_id <> people.pers_id)
or (peoplef.pers_id = friendships.friend_id and
peoplef.pers_id <> people.pers_id)
order by 2,4
sql小提琴演示：http://sqlfiddle.com/#!2/97b41/6/0
无论您是否在友谊表上记录两个方向,这都将有效.
