如何在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)