在sql中，可以使用select語句刪除重復數據，語法為：“select * from 字段 where 字段id in (select 字段id from 字段 group by 字段 having count(字段id) > 1)”。
本教程操作環境：windows7系統、mysql8.0版本、Dell G3電腦。
用sql語句,刪除掉重復項只保留一條
在幾千條記錄里,存在著些相同的記錄,如何能用SQL語句,刪除掉重復的呢
查找表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷?
select?*?from?people? where?peopleId?in?(select?peopleId?from?people?group?by?peopleId?having?count(peopleId)?>?1)
擴展：
刪除表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷，只留有rowid最小的記錄?
delete?from?people where???peopleName?in?(select?peopleName????from?people?group?by?peopleName??????having?count(peopleName)?>?1) and???peopleId?not?in?(select?min(peopleId)?from?people?group?by?peopleName?????having?count(peopleName)>1)
查找表中多余的重復記錄（多個字段）
select?*?from?vitae?a where?(a.peopleId,a.seq)?in?(select?peopleId,seq?from?vitae?group?by?peopleId,seq?having?count(*)?>?1)
?刪除表中多余的重復記錄（多個字段），只留有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)
查找表中多余的重復記錄（多個字段），不包含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)
消除一個字段的左邊的第一位：
update?tableName?set?[Title]=Right([Title],(len([Title])-1))?where?Title?like?'村%'
消除一個字段的右邊的第一位：
update?tableName?set?[Title]=left([Title],(len([Title])-1))?where?Title?like?'%村'
假刪除表中多余的重復記錄（多個字段），不包含rowid最小的記錄
update?vitae?set?ispass=-1where?peopleId?in?(select?peopleId?from?vitae?group?by?peopleId
相關推薦：《mysql教程》
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
