SQL语句查询和删除重复记录

最新推荐文章于 2023-08-24 09:49:05 发布

原创最新推荐文章于 2023-08-24 09:49:05 发布 · 460 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

20 篇文章

订阅专栏

博客介绍了在表中处理重复记录的方法，包括根据单个字段（title）和多个字段查找多余的重复记录，还涉及根据单个字段和多个字段查找并删除多余重复记录，仅保留rowid最小的记录。

根据单个字段（title）查找表中多余的重复记录

select * from article
where title in (select title from article group by title having count(title) > 1)

根据多个字段查找表中多余的重复记录

select * from article a
where (a.title,a.categoryid) in (select title,categoryid from article group by title, categoryid having count(*) > 1)

根据单个字段（title）查找并删除表中多余的重复记录，只留有rowid最小的记录

delete from article 
where title in (select title from article group by title having count(title) > 1)
and rowid not in (select min(rowid) from article group by title having count(title)>1)

根据多个字段查找并删除表中多余的重复记录，只留有rowid最小的记录

delete from article a
where (a.title,a.categoryid) in (select title, categoryid from article group by title, categoryid having count(*) > 1)
and rowid not in (select min(rowid) from article group by title, categoryid having count(*)>1)