在幾千條記錄里,存在著些相同的記錄,如何能用SQL語句,刪除掉重復的呢?
1、查找表中多余的重復記錄，重復記錄是根據(jù)單個字段（peopleId）來判斷
select?*?from?people
where?peopleId?in?(select??peopleId??from??people??group??by??peopleId??having??count(peopleId)?>?1)
2、刪除表中多余的重復記錄，重復記錄是根據(jù)單個字段（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)