SQL查询检查用户是否喜欢对方

Question

Hy我有一个名为match_liked的sql表，其中包含3个数据，即id、user_id和match_id

+----+---------+----------+
| id | user_id | match_id |
+----+---------+----------|
|  1 |     100 |       63 |
|  2 |      63 |       100|
|  3 |      45 |       77 |
|  4 |      11 |       44 |
|  5 |      33 |       2  |
+----+------+-------------+

你可以看到超过100点赞63和63点赞100，所以我想选择所有我尝试过的：

SELECT * from match_liked WHERE user_id = 100 AND match_id = 63 AND user_id = 63 AND match_id = 100

但它不起作用，那么如果两个用户都喜欢对方，正确的查询是什么呢？

Answer 1

一种方法使用exists:

select ml.*
from match_liked ml
where exists (select 1
from match_liked ml2
where ml2.user_id = ml.match_id and ml2.match_id = ml2.user_id
);

假设你没有重复，你也可以使用聚合：

select least(user_id, match_id), greatest(user_id, match_id)
from match_liked
group by least(user_id, match_id), greatest(user_id, match_id)
having count(*) = 2;

Answer 2

您可以使用自联接、

select o1.* from match_liked o1 inner join match_liked o2
on o1.user_id  = o2.match_id or o1.match_id = o2.user_id