在幾千條記錄里,存在著些相同的記錄,如何能用SQL語(yǔ)句,刪除掉重復(fù)的呢?
1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來(lái)判斷
select?*?from?people
where?peopleId?in?(select??peopleId??from??people??group??by??peopleId??having??count(peopleId)?>?1)
2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來(lái)判斷，只留有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、查找表中多余的重復(fù)記錄（多個(gè)字段）?
select?*?from?vitae?a
where?(a.peopleId,a.seq)?in??(select?peopleId,seq?from?vitae?group?by?peopleId,seq??having?count(*)?>?1)
4、刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有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、查找表中多余的重復(fù)記錄（多個(gè)字段），不包含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)