用PostgreSQL查找重复的行 [英] Find duplicate rows with PostgreSQL

问题描述

我们有一张照片列表，列表如下：

We have a table of photos with the following columns:

id, merchant_id, url 

此表包含组合 merchant_id，url 的重复值。所以有可能出现一行多次。

this table contains duplicate values for the combination merchant_id, url. so it's possible that one row appears more several times.

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

What is the best way to delete those duplications? (I use PostgreSQL 9.2 and Rails 3.)

推荐答案

这是我的接受。

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

随意按照顺序播放您要删除的记录到您的规格。

Feel free to play with the order by to tailor the records you want to delete to your specification.

SQL Fiddle => http ：//sqlfiddle.com/#！15 / d6941 / 1/0

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

Postgres的SQL小提琴9.2不再支持;更新SQL小提琴到postgres 9.3

SQL Fiddle for Postgres 9.2 is no longer supported; updating SQL Fiddle to postgres 9.3

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