SQL删除重复记录的N种方法
--<一>：
select distinct * into #temp from 表
truncate table 表
insert 表 select * from #temp
drop 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 a
from 表 a left join(select id=min(id) from 表 group by a,b)b on a.id=b.id

where b.id is null
go

-
-删除处理用的标识字段
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','4'
UNION ALL SELECT 2, '1','5','3','5'
UNION ALL SELECT 3, '1','2','7','9'
UNION ALL SELECT 4, '1','4','7','6'

delete Tb1 where [id] not in (select max([id]) from Tb1 group by a,b )
select * from tb1

drop table tb1

如果要同时删除第一和第三行
即如下结果：
a b c d
1 5 3 5


语句如下：

delete m from tb t
inner join
(
select a ,b
from tb
group by a , b
having count(*)>select distinct from1
)n
on m.a = n.a and m.b = n.b
或
delete * from tb as m,
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
where m.a = n.a and m.b = n.b
--在几千条记录里,存在着些相同的记录,如何能用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)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。