如何用SQL筛选People与Relation表中的双向互认关系？

核心思路 #

要筛选出双方互认的关系，本质是找到Relation表中**同时存在(id1, id2, r)和(id2, id1, r)**的记录对，再去重生成唯一的关系条目（避免重复存储1,2和2,1这类相同关系对）。

实现方案 #

方法1：自连接筛选+去重 #

通过将Relation表与自身连接，匹配反向且关系类型一致的记录，再通过id1 < id2确保每个关系对只保留一条：

-- 创建新表存储双向互认关系
CREATE TABLE MutualRelation AS
SELECT DISTINCT 
    LEAST(r1.id1, r1.id2) AS person_a,
    GREATEST(r1.id1, r1.id2) AS person_b,
    r1.r AS relation_type
FROM Relation r1
JOIN Relation r2 
    ON r1.id1 = r2.id2 
    AND r1.id2 = r2.id1 
    AND r1.r = r2.r;

• LEAST()和GREATEST()用来统一关系对的顺序，比如把(2,1,partner)转为(1,2,partner)，避免重复记录
• DISTINCT确保即使有多条重复的双向记录，最终只保留一条

方法2：EXISTS子查询验证 #

用子查询检查当前记录的反向关系是否存在，同样通过id1 < id2去重：

CREATE TABLE MutualRelation AS
SELECT DISTINCT 
    id1 AS person_a,
    id2 AS person_b,
    r AS relation_type
FROM Relation r1
WHERE id1 < id2
AND EXISTS (
    SELECT 1 
    FROM Relation r2 
    WHERE r2.id1 = r1.id2 
      AND r2.id2 = r1.id1 
      AND r2.r = r1.r
);

这种方法逻辑更直观，适合理解基础的存在性验证逻辑。

关联People表获取名称（可选） #

如果需要在结果中显示人名而非ID，可以关联People表：

CREATE TABLE MutualRelationWithNames AS
SELECT DISTINCT 
    p1.name AS name_a,
    p2.name AS name_b,
    r1.r AS relation_type
FROM Relation r1
JOIN Relation r2 
    ON r1.id1 = r2.id2 
    AND r1.id2 = r2.id1 
    AND r1.r = r2.r
JOIN People p1 ON r1.id1 = p1.id
JOIN People p2 ON r1.id2 = p2.id
WHERE p1.id < p2.id;

内容的提问来源于stack exchange，提问作者ArtificialCode