<一> 有相關(guān)ID標(biāo)識(shí)

一.查找重復(fù)記錄

1.查找所有重復(fù)記錄

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

? ?查出相同orderid中創(chuàng)建時(shí)間最晚的記錄

      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.過濾重復(fù)記錄(只顯示一條ID最大的)

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

??

二.刪除重復(fù)記錄

?

1.刪除全部重復(fù)記錄（慎用）?

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

??

2.保留一條,刪除其他重復(fù)記錄

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

??

3. 刪除多字段相同的重復(fù)記錄，保留一條

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

??

?

1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來判斷

      select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
    

??

2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（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、查找表中多余的重復(fù)記錄（多個(gè)字段）?

      select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
    

?
4、刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有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、查找表中多余的重復(fù)記錄（多個(gè)字段），不包含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)
    

??

?<二>沒有相關(guān)ID標(biāo)識(shí)

這種情況可以使用Row_Number() Over 來實(shí)現(xiàn)重復(fù)記錄的查詢和刪除：

原表：

查詢重復(fù)記錄:

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

    

?結(jié)果：

?刪除重復(fù)記錄:

      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;
    

?結(jié)果：

?三.根據(jù)兩個(gè)字段查重復(fù)

我想查詢表shiyan003,按xm,sfzhm這兩個(gè)字段查

      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是要查詢的重復(fù)字段
    

?