SQL刪除重復數據只保留一條

 　　用SQL語句，刪除掉重復項只保留一條

　　在幾千條記錄裡，存在著些相同的記錄，如何能用SQL語句，刪除掉重復的呢

　　1、查找表中多余的重復記錄，重復記錄是根據單個字段(peopleId)來判斷

　　select * from people

　　where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

　　2、刪除表中多余的重復記錄，重復記錄是根據單個字段(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)

　　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)

　　6.消除一個字段的左邊的第一位：

　　update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'

　　7.消除一個字段的右邊的第一位：

　　update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'

　　8.假刪除表中多余的重復記錄(多個字段)，不包含rowid最小的記錄

　　update vitae set ispass=-1

　　where peopleId in (select peopleId from vitae group by peopleId