SQL - 删除重复对 [英] SQL -- Remove duplicate pairs

问题描述

我使用SQLite来存储一组使用u和v两列的图的无向边。例如：

uv

1 2

<3>

2 1

3 4

我已经通过SELECT DISTINCT *所有重复的行。

但是，如果我们记住这些是无向边的，那么仍有重复。在上面的例子中，边缘（1,2）出现两次，一次是（1,2），一次是（2,1），两者都是相同的。

我希望删除所有这样的重复，只留下其中的一个，无论是（1,2）还是（2,1） - 哪个并不重要。

任何想法如何实现这一目标？谢谢！

解决方案

如果同一对（反转）存在，则使用u> v。

  SELECT DISTINCT u，v 
 FROM表t1 
 WHERE t1.u> t1.v 
 OR NOT NOT EXISTS（
 SELECT * FROM表t2 
 WHERE t2.u = t1.v AND t2.v = t1.u 
）
  

I'm using an SQLite to store a set of undirected edges of a graph using two columns, u and v. For example:

u v

1 2

3 2

2 1

3 4

I have already been through it with SELECT DISTINCT * FROM edges and removed all duplicate rows.

However, there are still duplicates if we remember these are undirected edges. In the above example, the edge (1,2) appears twice, once as (1,2) and once as (2,1) which are both equivalent.

I wish to remove all such duplicates leaving only one of them, either (1,2) or (2,1) -- it doesn't really matter which.

Any ideas how to achieve this? Thanks!

解决方案

If the same pair (reversed) exists take the one where u>v.

SELECT DISTINCT u,v
FROM table t1 
WHERE t1.u > t1.v
    OR NOT EXISTS (
        SELECT * FROM table t2 
            WHERE t2.u = t1.v AND t2.v = t1.u 
    )

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