使用PostgreSQL查找和删除重复行

Question

我们有一个照片表格，包含以下列：

id, merchant_id, url 

这个表格中包含了组合merchant_id, url的重复值。所以有可能一行会出现多次。

234 some_merchant  http://www.some-image-url.com/abscde1213
235 some_merchant  http://www.some-image-url.com/abscde1213
236 some_merchant  http://www.some-image-url.com/abscde1213

什么是删除这些重复项的最佳方法？ （我使用PostgreSQL 9.2和Rails 3。）

- schlubbi

---

你的ID列是唯一的吗？我看到234出现了3次，但你说你的merchant_id和url是重复的值。 - sgeddes

可能是https://dev59.com/h3I-5IYBdhLWcg3wpqIK的重复，请参考。 - user1914530

抱歉造成困惑。上面的示例中，ID 应该是唯一的。感谢您进行正确的编辑。这里 stackoverflow.com/questions/1746213/… 的解决方案对我的情况不起作用。 - schlubbi

Answer 1

这是我的看法。

select * from (
  SELECT id,
  ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row
  FROM Photos
) dups
where 
dups.Row > 1

可以随意使用order by来定制您想要删除的记录。

SQL Fiddle => http://sqlfiddle.com/#!15/d6941/1/0

---

不再支持在Postgres 9.2上使用SQL Fiddle；将SQL Fiddle更新到Postgres 9.3。

- MatthewJ

---

这个方法非常有效，但如何使用这个查询删除找到的重复项呢？ - Trasplazio Garzuglio

如果我们有同样的东西重复3次，那么结果会取2和3。我该如何解决它？ - Fausto Carvalho Marques Silva

这样做不会删除所有具有重复ID的记录吗？我认为OP想要保留一条记录。 - Daniël Tulp

不会的，这正是为什么要检查行数大于1。请参见SQL Fiddle。 - MatthewJ

作为一个非数据库专业人士，我认为这个解释非常好 https://www.postgresqltutorial.com/postgresql-row_number/ - Miguel Lattuada

Answer 2

sgeddes的答案的第二部分在Postgres上无法运行（示例使用的是MySQL）。这里是他的答案的更新版本，使用Postgres：http://sqlfiddle.com/#!12/6b1a7/1

DELETE FROM Photos AS P1  
USING Photos AS P2
WHERE P1.id > P2.id
   AND P1.merchant_id = P2.merchant_id  
   AND P1.url = P2.url;  

- 11101101b

Answer 3

我可以为您提供帮助。以下是您的几个选项。

如果您想快速完成，请使用以下代码（假设您的ID列不是唯一的，因为您在上面多次提到了234）：

CREATE TABLE tmpPhotos AS SELECT DISTINCT * FROM Photos;
DROP TABLE Photos;
ALTER TABLE tmpPhotos RENAME TO Photos;

这里是SQL Fiddle。

如果有约束条件，需要将其添加回表中。

如果您的ID列是唯一的，您可以执行以下操作以保留最低ID：

DELETE FROM P1  
USING Photos P1, Photos P2
WHERE P1.id > P2.id
   AND P1.merchant_id = P2.merchant_id  
   AND P1.url = P2.url;  

并且这里有一个示例。

- sgeddes

---

ID在我的情况下是唯一的。我在示例代码中只是做错了。但是如果我尝试使用您的第二个解决方案，就会出现错误。 错误：关系“p1”不存在 - schlubbi

@StefanSchmidt 我已经修复了它，现在可以在Postgres上运行，而不是MySQL：http://sqlfiddle.com/#!12/6b1a7/1 - 11101101b