sql - 自加入多对多关系

Question

根据下面的示例数据，假设 Julie (1) 有 friend Adam、David、John (2、3、4)。Adam (2) 有 friend Julie、David、John (1、3、4)。

ID  Name
1   Julie
2   Adam
3   David
4   John
5   Sam

这会在一个表中建立自连接和多对多关系。

除了上述问题，假设 Julie (1) 添加 Sam (5) 作为好友，从技术上和实践上来说，Sam (5) 现在也是 Julie (1) 的好友。这使事情变得更加复杂，因为关系是双向的。

所以我想知道:

1. 如何设计数据库？
2. 如何进行查询以返回每个用户的所有好友？

谢谢!

Answer 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 Fiddle 演示:http://sqlfiddle.com/#!2/97b41/6/0

无论您是否在友谊表上记录两个方向，这都会起作用。