SQL -- 删除重复的对。

Question

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

1 2

3 2

2 1

3 4

我已经通过SELECT DISTINCT * FROM edges查询并删除了所有重复行。

但是，如果我们记得这些是无向边，则仍然存在重复项。在上面的示例中，边缘（1,2）出现两次，一次为（1,2），一次为（2,1），它们都是等价的。

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

有什么好的方法可实现此目的吗？谢谢！

- Jonathan Ellis

Answer 1

如果存在相同的一对（反向）值，选择其中大于小于的那一个。

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 
    )

- mostar

---

我使用了这个查询的变体来帮助我找到共享边缘的多边形。在使用这个查询之前，我的问题是结果数量翻了一倍，在第一列中有parcel_id_1，在第二列中有parcel_id_2，但在下一行中也反过来了。 SELECT DISTINCT v1.pin14 FROM parcels v1 WHERE EXISTS ( SELECT v2.pin14 FROM parcels v2 WHERE st_touches(v1.geom, v2.geom) ) - stevevance

Answer 2

这将找到所有的重复项：

SELECT t1.u, t1.v FROM table t1 INNER JOIN table t2
 ON t1.u = t2.v AND t1.v = t2.u

这将删除重复项：

DELETE FROM table t1 WHERE
  EXISTS (SELECT * FROM table t2 WHERE t2.u = t1.v AND t2.v = t1.u AND t1.u > t2.u)

请注意，这不会删除像（2,2）这样的重复项，但我认为您已经使用SELECT DISTINCT获得了这些内容。

- Larry Lustig

---

您可能需要澄清您正在选择哪个“u”和“v”。 - Jan Krüger

这可能是一个愚蠢的问题，但你能澄清一下你所说的“table t1”是什么意思吗？这是指“table AS t1”的简写形式，其中table是表的名称吗？因为我不认为SQLite支持这种语法--http://www.sqlite.org/lang_delete.html。 - Jonathan Ellis

确实，你是对的Jonathan。我需要进一步研究一下，但我认为你可以在第一行删除t1别名，将其余的“t1”更改为“table”，并保留t2别名。不过我还需要试一下。 - Larry Lustig

好的，完成了第一部分：SELECT u AS u1, v AS v1 FROM edges WHERE EXISTS (SELECT * FROM edges WHERE u=v1 AND v=u1 AND u > v)。这将返回需要删除的重复项。现在我只需要将其传递给DELETE，它应该可以工作，只是不确定如何做到这一点！（这是对我之前发布的替代方案进行的轻微修改，添加了u>v的约束条件，因此仅返回单个行，并从您的方案中添加了>约束条件）。 - Jonathan Ellis

在线查了一下，似乎SQLite中不可能做到这点，可能需要运行SELECT将行获取到临时表中，然后使用DELETE... 不是最优的解决方案，但只需要执行一次，所以应该可以！ - Jonathan Ellis

Answer 3

测试9个数字，我将9个数字添加到两个表中：

 declare @num  int
 set @num =1
 while @num<10
 begin 
 insert into t2 values (@num)
 insert into t1 values (@num)
 set @num +=  1 
 end

然后将不重复的耦合唯一性返回：

 select t1.u, t2.v
 from t1 cross join t2
 where t1.u>t2.v

- ido