sql - MySQL查询:我需要不同用户之间的所有“友谊”

我需要有关mysql查询的帮助。查询的输出应该如下所示

User1       User2
xxx1        xxx2
xxx3        xxx1

但不是

User1       User2
xxx1        xxx2
xxx2        xxx1

等等。
我需要不同用户之间的所有“友谊”。友谊存在，即

UserId 8 exists in Column User1_id
UserId 4 exists in Column User2_id
**AND**
UserId 8 exists in Column User2_id
UserId 4 exists in Column User1_id

友谊桌

+----------+----------+
| User1_id | User2_id |
+----------+----------+
|        8 |        4 |
|        4 |        8 |
|       29 |        4 |
|        4 |       10 |
|       10 |        4 |
|        8 |       37 |
|        4 |       29 |
|       37 |        8 |
|       37 |        4 |
|       29 |        8 |
|        4 |       37 |
|        8 |       10 |
|        8 |       29 |
|        4 |       40 |
|       40 |        4 |
|       40 |       29 |
|       29 |       40 |
+----------+----------+

用户表

+----+-----------------------------+
| id | username                    |
+----+-----------------------------+
|  4 | hhessel                     |
|  8 | xxx1                        |
| 10 | xxx2                        |
| 29 | xxx3                        |
| 40 | xxx4                        |
| 37 | xxx5                        |
| 39 | xxx6                        |
+----+-----------------------------+

最佳答案

使用

SELECT a.user1_id,
       a.user2_id
  FROM FRIENDSHIP a
  JOIN FRIENDSHIP b ON b.user2_id = a.user1_id
                   AND b.user1_id = a.user2_id
                   AND b.user1_id > a.user1_id

如果要反转列值，则必须反转user1_id比较：

AND b.user1_id < a.user1_id

关于sql - MySQL查询:我需要不同用户之间的所有“友谊”，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3561346/