SQL删除重复记录的N种方法.doc

SQL删除重复记录的N种方法-：select distinct * into #temp from 表truncate table 表insert 表 select * from #tempdrop table-带有标识列-备份数据select * into #temp from 表alter table #temp drop column id-删除原表数据truncate table msgtable-恢复数据并去掉重复数据insert into 表 select distinct * from #temp-delete From aa where a in ( select a From aa group by a having count(a)1)-alter table 表 add newfield int identity(1,1)delete 表where newfield not in(select min(newfield) from 表 group by 除newfield外的所有字段)alter table 表 drop column newfield-添加一个处理的标识字段alter table 表 add id int identity(1,1)go -删除重复记录delete afrom 表 a left join(select id=min(id) from 表 group by a,b)b on a.id=b.idwhere b.id is nullgo-删除处理用的标识字段alter table 表 drop column id-参考：/*一张表里面以两个字段为唯一字段，当几条记录的这两个字段完全相同时，需要删除重复项，如下表 a b c d 1 2 3 4 1 5 3 5 1 2 7 9 以a、b为唯一字段，第一条和第三条的a、b完全相同，所以，需要删除第一条记录1 2 3 4 或者第三条记录1 2 7 9 即如下结果： a b c d 1 2 3 4 1 5 3 5 或 a b c d 1 5 3 5 1 2 7 9 请问各位大侠这种sql语句怎么写 */ CREATE TABLE Tb1(id int, a varchar(255), b varchar(255), c varchar(255), d varchar(255)INSERT Tb1(id, a, b, c, d)SELECT 1, 1,2,3,4UNION ALL SELECT 2, 1,5,3,5UNION ALL SELECT 3, 1,2,7,9UNION ALL SELECT 4, 1,4,7,6delete Tb1 where id not in (select max(id) from Tb1 group by a,b )select * from tb1drop table tb1如果要同时删除第一和第三行即如下结果：a b c d1 5 3 5语句如下：delete m from tb tinner join(select a ,bfrom tbgroup by a , bhaving count(*)1)non m.a = n.a and m.b = n.b或delete * from tb as m,(select a ,bfrom tbgroup by a , bhaving count(*)1)nwhere m.a = n.a and m.b = n.b-在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢!1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录delete from peoplewhere 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 awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录delete from vitae awhere (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 awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae