sql - 相互喜欢的 SQL

Question

match
---
id person_1_id persona_2_id liked
9  1           2            true
10 1           3            false
11 2           1            true
12 2           3            true
13 3           1            false
14 3           2            false

person_1_id是person_2_id喜欢的人，也是被喜欢的人。

我将如何查询相互喜欢的内容person id = 1？意思是person_1_id = 1喜欢person_2_id并persona_2_id喜欢他们。预期结果将是包含person_id = 2.

标签： sqlpostgresql

Answer 1

使用自加入：

SELECT DISTINCT t1.person_1_id, t1.person_2_id
FROM mytable t1
INNER JOIN mytable t2 
    ON t1.person_1_id = t2.person_2_id 
    AND t1.person_2_id = t2.person_1_id
    AND t2.liked = true
WHERE t1.liked = true;

或具有EXISTS条件的相关子查询：

SELECT DISTINCT t1.person_1_id, t1.person_2_id
FROM mytable t1
WHERE 
    EXISTS (
        SELECT 1 
        FROM mytable t2 
        WHERE 
            t1.person_1_id = t2.person_2_id 
            AND t1.person_2_id = t2.person_1_id
            AND t2.liked = true
    )
    AND t1.liked = true;