表中熟人的SQL View

Question

我有一张 friend table

PLAYER_ID    FRIEND_ID
----------------------
1            2
1            3         // 1 knows 3
3            1         // 3 knows 1
2            4

如何为给定的玩家创建一个包含那些熟人(彼此认识)的 View ？

PLAYER_ID    FRIEND_ID
----------------------
1            3
3            1

谢谢你。

Answer 1

这是所需的选择:

select t1.PLAYER_id, t1.FRIEND_ID from tablename t1
inner join tablename t2 on t1.PLAYER_id = t2.FRIEND_ID and t2.PLAYER_id = t1.FRIEND_ID