SQL server 经验分享(五)重复记录的查询与删除

<一> 有相关ID标识

一.查找重复记录

1.查找所有重复记录

Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
 
SELECT *
FROM CompanyEmail c
WHERE ((SELECT COUNT(*)
FROM CompanyEmail  
WHERE Email= a.Email) > 1)
ORDER BY Email DESC

   查出相同orderid中创建时间最晚的记录

select t.Id from  Group_Require t,
(select max(CreateTime) x from Group_Require group by OrderId) 
xx where t.CreateTime=xx.x and t.OrderId=38

 

2.过滤重复记录(只显示一条ID最大的)

Select * From CompanyEmail  Where ID In (Select Max(ID) From CompanyEmail  Group By Email)

  

二.删除重复记录

 

1.删除全部重复记录（慎用） 

Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)

  

2.保留一条,删除其他重复记录

Delete CompanyEmail Where ID Not In (Select Max(ID) From CompanyEmail Group By Email)

  

3. 删除多字段相同的重复记录，保留一条

delete CompanyEmail where ID not in (select max(ID) from CompanyEmail group by Email, Company)

  

 

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)

  

 <二>没有相关ID标识

这种情况可以使用Row_Number() Over 来实现重复记录的查询和删除：

原表：

查询重复记录:

Select Row_Number(),* Over(Partition By userName,userPwd order By userName) From [Temp].[dbo].[User];

 结果：

 删除重复记录:

Delete T From
(Select Row_Number() Over(Partition By userName,userPwd order By userName) As RowNumber,* From [Temp].[dbo].[User])T
Where T.RowNumber > 1;

 结果：

 三.根据两个字段查重复

我想查询表shiyan003,按xm,sfzhm这两个字段查

select *
  from shiyan003 a
 where exists (select 1
          from (select xm, sfzhm
                  from shiyan003
                 group by xm, sfzhm
                having count(*) > 1) s
         where s.xm = a.xm
           and s.sfzhm = a.sfzhm)

 

select * from 表名 a join (select ID,NAME from 表名 group by ID,NAME having count(*)>1) b on a.ID=b.ID and a.NAME=b.NAME order by a.NAME ,a.ID
ID和NAME是要查询的重复字段