使用 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 9.2 的 SQL Fiddle;将 SQL Fiddle 更新到 postgres 9.3
SQL Fiddle for Postgres 9.2 is no longer supported; updating SQL Fiddle to postgres 9.3
这篇关于使用 PostgreSQL 查找重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!