如何在sql表中查找两条重复记录

如何在sql表中查找两条重复记录

查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people
where id in (select id from people group by id having count (id) > 1)

思路
将表中的数据按照需要的字段进行分组（例如id）,最后使用having关键字筛选记录数大于1的记录，即为重复的表数据、

having和where的区别
where和having皆是用于对一条SQL语句进行条件限定的关键字，不过where用于group by之前，要先where再group by；having用于group by之后，先group by再having。

删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

delete from people 
where id in (select id from people group by id having count(id) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(id)>1)

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

select * from vitae a
where (a.id,a.seq) in   (select id,seq from vitae group by id,seq having count(*) > 1)

删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from vitae a
where (a.id,a.seq) in   (select id,seq from vitae group by id,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by id,seq having count(*)>1)