从 SQL 连接中删除镜像对 [英] Removing Mirrored Pairs from SQL Join

问题描述

我有一个包含 2 个字段(名称、兴趣)的表，我想找到所有具有相同兴趣的对，并删除所有重复项和镜像对.

I have a table with 2 fields (name, interest) and I want to find all pairs that have the same interest, with all duplicates and mirrored pairs removed.

我可以使用以下 SQL 语句找到所有对并删除重复项:

I am able to find all pairs and remove duplicates with the following SQL statement:

SELECT P1.name AS name1, P2.name AS name2, P1.interest 
FROM Table AS P1, Table AS P2
WHERE P1.interest = P2.interest AND P1.name <> P2.name;

但我不确定如何删除镜像对，即:

But I am not sure how to remove mirrored pairs, ie:

"wil","ben","databases"

"ben","wil","databases"

我尝试将上述语句设为名为 Matches 的视图并尝试执行以下查询:

I tried to make the above statement a view called Matches and attempted the following query:

SELECT * FROM Matches
WHERE name2 <> (select name1 from Matches);

但它不会删除所有镜像对.

But it does not remove all mirrored pairs.

推荐答案

假设你不关心哪一对最终坚持 (ben,will) vs (will, ben)，那么我的首选解决方案是执行以下操作:

Assuming you do not care which pair ends up sticking around (ben,will) vs (will, ben), then my preferred solution is to do the following:

DELETE p2 
FROM Pairs p1 
INNER JOIN Pairs p2 
    on p1.Name1 = p2.Name2 
    and p1.Name2 = p2.Name1 
    and p1.Interest = p2.Interest
    -- match only one of the two pairs
    and p1.Name1 > p1.Name2

由于您永远不会让 Name1 和 Name2 相等，因此必须始终存在一对，其中第一个成员小于第二个成员.使用这种关系，我们可以删除重复项.

By virtue of the fact that you would never have Name1 and Name2 equal, there must always be one pair where the first member is less than the second member. Using that relationship, we can delete the duplicate.

如果您有关系的代理键，这尤其微不足道，因为 Name1 和 Name2 不相等的要求就消失了.

This is especially trivial if you have a surrogate key for the relationship, as then the requirement for Name1 and Name2 to be unequal goes away.

如果您不想从表中删除它们，而只想从特定查询的结果中删除它们，请对 SELECT 使用相同的模式，而不是删除.

if you don't want to remove them from the table, but just from the results of a specific query, use the same pattern with SELECT rather than DELETE.

这篇关于从 SQL 连接中删除镜像对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！