如何在Postgres中删除两个重复的数据行之一？ [英] How do I delete one of my two duplicate rows of data in Postgres?

问题描述

我正在使用Postgres 9.5。我有下面的查询，该查询旨在在我的表中查找相同的数据行（但唯一的ID）。

I’m using Postgres 9.5. I have the below query that is designed to find identical rows of data (but unique IDs) in my table.

select e.name, 
       e.day, 
       e.distance, 
       e.created_at, 
       e2.created_at 
from events e, 
     events e2 
where e.name = e2.name 
  and e.distance = e2.distance 
  and e.day = e2.day 
  and e.web_crawler_id = e2.web_crawler_id 
  and e.id <> e2.id 
  and e.web_crawler_id = 1 
order by e.day desc;

我最终想删除重复的行之一，因此也许要删除 created_at值最大的行日期。但是我不确定如何编写查询以仅返回两个相同行之一。我该怎么做？

I ultimately want to delete one of the duplicate rows — so perhaps deleting the row with the greatest "created_at" date. But I’m unsure how to write a query to only return one of the two identical rows. How do I do that?

推荐答案

有很多方法，但是无需更改SQL，您可以做很多事而不是< ;> for id：

There are many ways but without changing your sql much you can just do greater than instead of <> for id:

select e.name, e.day, e.distance, e.created_at, e2.created_at 
from events e, events e2
where e.name = e2.name
and e.distance = e2.distance
and e.day = e2.day 
and e.web_crawler_id = e2.web_crawler_id 
and e.id > e2.id 
and e.web_crawler_id = 1 

这篇关于如何在Postgres中删除两个重复的数据行之一？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！