sql - 如何在 SQL 中选择相互 Tinder 匹配？

Question

理论上说我们有一个名为 matches 的表(带有一些测试数据):

create table matches (
  user_id int,
  target_id int
);
INSERT INTO matches VALUES (1,2);
INSERT INTO matches VALUES (2,1);
INSERT INTO matches VALUES (3,5);
INSERT INTO matches VALUES (4,1);
INSERT INTO matches VALUES (1,4);
INSERT INTO matches VALUES (5,3);
INSERT INTO matches VALUES (5,4);
INSERT INTO matches VALUES (4,5);
INSERT INTO matches VALUES (1,3);
INSERT INTO matches VALUES (1,5);
INSERT INTO matches VALUES (6,1);
INSERT INTO matches VALUES (6,2);
INSERT INTO matches VALUES (6,3);
INSERT INTO matches VALUES (6,4);
INSERT INTO matches VALUES (6,5);
INSERT INTO matches VALUES (6,6); // me_irl

因此对于这个测试数据集，我们应该期待以下相互 Tinder 匹配:

1,2
4,1
5,3
5,4

我如何在 SQL 中选择它？这是我得到的，它有效但选择重复项:

select A.user_id A, B.user_id B from
matches as A
inner join 
matches as B
on A.user_id = B.target_id
where A.user_id = B.target_id
and B.user_id = A.target_id

这是 SQL 查询返回的内容:

A   B
1   2
1   4
2   1
3   5
4   1
4   5
5   3
5   4

Answer 1

您可以通过添加“a.user_id < b.user_id”来过滤掉重复项。

像这样:

select A.user_id A, B.user_id B from
matches as A
inner join 
matches as B
on A.user_id = B.target_id
where A.user_id = B.target_id
and B.user_id = A.target_id
and a.user_id < b.user_id