使用 PostgreSQL 查找重复行

我们有一张照片表，其中有以下几栏:

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。)

69043 次浏览
小开

我看到你有几个选择。

为了快速完成这项工作，可以这样做(它假设您的 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;

还有 小提琴。

小开
最佳答案

以下是我对此的看法。

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

您可以根据自己的规范随意调整要删除的记录。

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

不再支持 Postgres 9.2的 SQL Fiddle; 将 SQL Fiddle 更新为 Postgres 9.3

小开

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;

提交答案