查找并删除可以在两个列之间交换值的重复条目

Question

我有以下数据结构，其中包含节点之间的关系。relationships表中有from_id和to_id，它们的值可以交换

我需要找到并删除重复的，只留下每对的一行。

| id    | node_from_id  | node_to_id |
---------------------------------
| 1     |   100         |   200      |
| 2     |   200         |   100      |
| 3     |   200         |   300      |
| 4     |   300         |   200      |
| 5     |   200         |   300      |
| 6     |   300         |   400      |
| 7     |   500         |   400      |

删除重复项后，我希望以下内容保留在表

中

| id    | node_from_id  | node_to_id |
---------------------------------
| 1     |   100         |   200      |
| 3     |   200         |   300      |
| 6     |   300         |   400      |
| 7     |   500         |   400      |

我能够返回相对简单匹配的所有行，但为了使它们能够被删除，我只想返回实际的重复，以允许其中一个键对行保留。

这是我的查询，目前返回所有匹配的行

SELECT *
FROM relationships AS rel1
WHERE EXISTS (SELECT *
    FROM relationships AS rel2
    WHERE
        rel1.id <> rel2.id
    AND
    (
        (
            rel1.from_id = rel2.from_id AND
            rel1.to_id = rel2.to_id 
        )
        OR
        (
            rel1.from_id = rel2.to_id AND
            rel1.to_id = rel2.from_id
        )
    )
)

我想我可能能够使用窗口函数并仅选择row_number() > 1的行，但我似乎无法在EXISTS子查询中使用它。

如果有帮助，我已经将relationships的实际数据作为CSV提供

Answer 1

我会用像

select  min(id),
        least (node_from_id, node_to_id) node_from_id,
        greatest(node_from_id, node_to_id) node_to_id
from    relationships
group   by         
        least (node_from_id, node_to_id) ,
        greatest(node_from_id, node_to_id) 

Answer 2

-- SELECT * -- to test  which rows will be deleted
DELETE      -- to actually delete     
FROM ztable zt
WHERE zt.node_from_id > zt.node_to_id
AND EXISTS(
        select *
        FROM ztable nx
        WHERE nx.node_from_id = zt.node_to_id
        AND nx.node_to_id = zt.node_from_id
        );