(轉)刪除數據庫中的重複記錄

布满荆棘的人生 2022-08-27 10:44 112阅读 0赞

(轉)刪除數據庫中的重複記錄  
本文轉自:http://blog.csdn.net/Mobidogs/archive/2007/05/15/1610498.aspx

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 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)

比方说在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，  
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；  
Select Name,Count(\*) From A Group By Name Having Count(\*) > 1