SQL server删除重复数据解决方案.docVIP

SQL删除重复数据的解决方案 例如： id?????????? name???????? value 1?????????????? a???????????????? pp 2?????????????? a???????????????? pp 3?????????????? b???????????????? iii 4?????????????? b???????????????? pp 5?????????????? b???????????????? pp 6?????????????? c???????????????? pp 7?????????????? c???????????????? pp 8?????????????? c???????????????? iii id是主键 要求得到这样的结果 id?????????? name???????? value 1?????????????? a???????????????? pp 3?????????????? b???????????????? iii 4?????????????? b???????????????? pp 6?????????????? c???????????????? pp 8?????????????? c???????????????? iii 方法1 delete?? YourTable?? where?? [id]?? not?? in?? ( select?? max([id])?? from?? YourTable?? group?? by?? (name?? +?? value)) 方法2 delete?? a from?? 表?? a?? left?? join( select?? id=min(id)?? from?? 表?? group?? by?? name,value )b?? on?? a.id=b.id where?? b.id?? is?? null 查询及删除重复记录的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 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”，