英文:
Remove duplicate records with same ID - Postgresql
问题
你可以使用以下 SQL 查询来保留重复记录中的一个，前提是它们具有相同的 ID：
WITH RankedLocations AS (
SELECT id, name, point, closest_community_id,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS RowNum
FROM primary_location pl
)
DELETE FROM RankedLocations
WHERE RowNum > 1;
这将使用窗口函数 ROW_NUMBER 对每个相同 ID 的记录进行编号，然后删除编号大于 1 的记录，从而保留每个 ID 组中的一个记录。
英文:
Wondering if it is possible to keep just one record from duplicates. In this scenario, the ID is also duplicate. I know the best way is that ID is unique, but in this scenario is not. Then, I have three records like these ones:
| id | name | point | closest_community_id |
|---|---|---|---|
| 1 | Secondary School | POINT (-121.94291423 49.17457331) | 126 |
| 1 | Secondary School | POINT (-121.94291423 49.17457331) | 126 |
| 1 | Secondary School | POINT (-121.94291423 49.17457331) | 126 |
I can find the duplicates with this query:
select id, name, point, closest_community_id
from primary_location pl
group by pl.id, name, point, closest_community_id
having count(pl.id) > 1
But how can I keep just one record from the duplicates when they have the same ID?
答案1
得分: 2
使用ctid：
delete from primary_location pl
where exists
(
select null
from primary_location pl2
where pl2.id = pl.id
and pl2.name = pl.name
and pl2.point = pl.point
and pl2.closest_community_id = pl.closest_community_id
and pl2.ctid > pl.ctid
);
英文:
Use the ctid:
delete from primary_location pl
where exists
(
select null
from primary_location pl2
where pl2.id = pl.id
and pl2.name = pl.name
and pl2.point = pl.point
and pl2.closest_community_id = pl.closest_community_id
and pl2.ctid > pl.ctid
);
答案2
得分: 0
你想要尝试
从 primary_location pl 中选择 distinct id, name, point, closest_community_id
英文:
Would you like to try
select distinct id, name, point, closest_community_id
from primary_location pl
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论