如何去除自连接查询结果中的重复配对记录

我将reviewers表与ratings表关联后生成的结果表进行自连接操作，查询结果中出现了重复的配对记录（如下表所示），例如(Alice Lewis, Elizabeth Black)与(Elizabeth Black, Alice Lewis)这类互为反向的配对。当前使用的SQL语句如下：

select DISTINCT A.name as name1 , B.name as name2
from (select name , ratings.* from reviewers inner join ratings on reviewers.id = 
ratings.reviewer_id ) A ,
(select name , ratings.* from reviewers inner join ratings on reviewers.id = 
ratings.reviewer_id ) B
where A.reviewer_id <> B.reviewer_id 
and A.book_id = B.book_id
order by name1 , name2 ASC 

查询结果：

修改方案 #

核心是通过单向ID比较避免反向配对，将原条件中的A.reviewer_id <> B.reviewer_id替换为A.reviewer_id < B.reviewer_id（或A.reviewer_id > B.reviewer_id，效果一致），这样每对用户只会以ID较小者在前的形式出现一次，不会生成反向重复记录。

同时可以简化原SQL的子查询结构，直接关联原表提升效率，修改后的语句如下：

SELECT DISTINCT A.name AS name1, B.name AS name2
FROM reviewers A
JOIN ratings ra ON A.id = ra.reviewer_id
JOIN reviewers B
JOIN ratings rb ON B.id = rb.reviewer_id
WHERE A.id < B.id 
  AND ra.book_id = rb.book_id
ORDER BY name1, name2 ASC;

如果要保留原有的子查询写法，仅修改条件即可：

SELECT DISTINCT A.name AS name1, B.name AS name2
FROM (SELECT name, ratings.* FROM reviewers INNER JOIN ratings ON reviewers.id = ratings.reviewer_id) A,
     (SELECT name, ratings.* FROM reviewers INNER JOIN ratings ON reviewers.id = ratings.reviewer_id) B
WHERE A.reviewer_id < B.reviewer_id 
  AND A.book_id = B.book_id
ORDER BY name1, name2 ASC;

说明 #

• 使用<或>替代<>，确保每对配对仅保留单向组合，彻底消除反向重复
• 保留DISTINCT可以避免同一用户对同一本书多次评分导致的重复记录（如果ratings表中存在同一reviewer_id和book_id的多条记录）

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