<一> 有相關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是要查詢的重復字段
    

?