如何在 PostgreSQL 中查找重复记录 [英] How to find duplicate records in PostgreSQL
问题描述
我有一个名为user_links"的 PostgreSQL 数据库表,它目前允许以下重复字段:
I have a PostgreSQL database table called "user_links" which currently allows the following duplicate fields:
year, user_id, sid, cid
唯一约束目前是第一个名为id"的字段,但是我现在希望添加一个约束以确保 year
、user_id
、sid
和 cid
都是唯一的,但我无法应用该约束,因为已经存在违反此约束的重复值.
The unique constraint is currently the first field called "id", however I am now looking to add a constraint to make sure the year
, user_id
, sid
and cid
are all unique but I cannot apply the constraint because duplicate values already exist which violate this constraint.
有没有办法找到所有重复项?
Is there a way to find all duplicates?
推荐答案
基本思想是使用带有计数聚合的嵌套查询:
The basic idea will be using a nested query with count aggregation:
select * from yourTable ou
where (select count(*) from yourTable inr
where inr.sid = ou.sid) > 1
您可以调整内部查询中的 where 子句以缩小搜索范围.
You can adjust the where clause in the inner query to narrow the search.
对于评论中提到的问题,还有另一个很好的解决方案,(但不是每个人都会阅读):
There is another good solution for that mentioned in the comments, (but not everyone reads them):
select Column1, Column2, count(*)
from yourTable
group by Column1, Column2
HAVING count(*) > 1
或更短:
SELECT (yourTable.*)::text, count(*)
FROM yourTable
GROUP BY yourTable.*
HAVING count(*) > 1
这篇关于如何在 PostgreSQL 中查找重复记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!