MySQL删除重复记录的方法

数据库 MySQL
如果出现重复记录,就需要进行删除操作,下面为您介绍了几种MySQL删除重复记录的方法,希望对您能够有所帮助。

MySQL删除重复记录是我们经常用到的操作,下面为您介绍了5种MySQL删除重复记录的方法,如果您感兴趣的话,不妨一看。

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)
and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)

3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
 

 

 

【编辑推荐】

使用MySQL临时表加速查询

教您如何合理使用MySQL索引

5种常见的MySQL日志类型

自动恢复MySQL日志文件的方法

如何使用新的MySQL更新日志

 

责任编辑:段燃 来源: 互联网
相关推荐

2010-11-23 14:26:02

MySQL删除重复记录

2010-10-13 17:13:17

MySQL重复记录

2010-10-27 16:49:23

Oracle删除重复记

2010-11-25 15:43:02

MYSQL查询重复记录

2010-09-03 09:49:39

SQL删除

2011-05-24 10:04:39

Oracle重复记录

2010-09-25 16:17:25

SQL语句

2010-09-03 11:42:04

SQL删除

2010-10-27 16:56:05

Oracle重复记录

2010-09-28 15:46:22

SQL删除重复记录

2010-09-28 15:40:51

SQL删除重复记录

2011-03-04 17:30:42

Oracle数据库

2010-07-26 17:00:11

SQL Server查

2010-09-30 10:29:56

DB2删除重复记录

2010-11-15 14:42:03

Oracle查询重复记

2010-04-13 10:23:40

Oracle数据库

2011-05-17 11:23:02

Oracle数据库

2018-09-11 17:13:23

MySQ数据库重复记录

2010-07-02 13:50:11

SQL Server数

2011-03-21 17:25:08

SQL Server数重复记录
点赞
收藏

51CTO技术栈公众号