一张person表,有id和name的两个字段,id是唯一的不允许重复,id相同则认为是重复的记录。
select id from group by id having count(*) > 1
查找表中多余的重复记录,重复记录是根据单个字段(Id)来判断
select?*?from?表?where?Id?in?(select?Id?from?表?group?byId?having?count(Id)?>?1)
删除表中多余的重复记录,重复记录是根据单个字段(Id)来判断,只留有rowid最小的记录
DELETE?from?表?WHERE?(id)?IN?(?SELECT?id?FROM?表?GROUP?BY?id?HAVING?COUNT(id)?>?1)?AND?ROWID?NOT?IN?(SELECT?MIN(ROWID)?FROM?表?GROUP?BY?id?HAVING?COUNT(*)?>1);
查找表中多余的重复记录(多个字段)
select?
?from?表?a?where?(a.Id,a.seq)?in(select?Id,seq?from?表?group?by?Id,seq?having?
count()?>?1)
删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete?
from?表?a?where?(a.Id,a.seq)?in?(select?Id,seq?from?表?group?by?Id,seq?having?
count()?>?1)?and?rowid?not?in?(select?min(rowid)?from?表?group?by?Id,seq?
having?count()>1)
查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select?
?from?表?a?where?(a.Id,a.seq)?in?(select?Id,seq?from?表?group?by?Id,seq?having?
count()?>?1)?and?rowid?not?in?(select?min(rowid)?from?表?group?by?Id,seq?
having?count(*)>1)
原文:https://www.cnblogs.com/jichi/p/11227622.html