sql - MySQL 查询 : I need all "friendships" between different users

Question

我需要有关 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                        | 
+----+-----------------------------+

Answer 1

使用:

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