SQL Server Delete duplicate record processing (EXT) _ Practical Tips

Note: "Repeat" here is not a complete repetition, meaning a field data duplication
HZT table Structure
ID int
Title nvarchar (50)
Adddate datetime
Data
One. Find duplicate records
1. Find all duplicate records
SELECT * FROM table Where repeat field in (Select repeating field from table Group by repeating field having Count (*) >1)

2. Filter duplicate records (show only one)
SELECT * from Hzt Where ID in (select Max (ID) from Hzt Group by Title)
Note: A record with the largest ID is shown here
Two. Delete duplicate records
1. Delete all duplicate records (use caution)
Delete table Where Repeat field in (Select repeating field from table Group by repeating field having Count (*) >1)

2. Keep One (this should be what most people need)
Delete hzt Where ID. (Select Max (ID) from Hzt Group by Title)
Note: A record with the largest ID is reserved here

Other Related:

To delete a duplicate record when there is a size relationship, keep one of the records large or small

-->--> (Roy) generates test data

If Not object_id('Tempdb.. #T')Is Null
 Drop Table#T
Go
Create Table#T ([Id] Int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert#T
Select 1N'A'N'A1' Union Cl.
Select 2N'A'N'A2' Union Cl.
Select 3N'A'N'A3' Union Cl.
Select 4N'B'N'B1' Union Cl.
Select 5N'B'N'B2'
Go

--I, name the same ID minimum record (recommended with 1,2,3), keep the smallest one
Method 1:
DeleteAFrom#T Awhere Exists(Select 1 From#TwhereName=A.nameandId<a.ID)
Method 2:
DeleteAFrom#T ALeft Join(Select Min(ID) Id,nameFrom#TGroup ByName) bOnA.name=B.nameanda.ID=b.ID
whereb.IDIs Null
Method 3:
DeleteAFrom#T AwhereIdNot In(Select Min(ID)From#TwhereName=A.name)
Method 4 (Note: Available if ID is unique):
DeleteAFrom#T AwhereIdNot In(Select Min(ID)From#TGroup ByName)
Method 5:
DeleteAFrom#T Awhere(Select Count(1)From#TwhereName=A.nameandId<a.ID)>0
Method 6:
DeleteAFrom#T AwhereId<>(Select Top 1IdFrom#TwhereName=A.nameOrder ByID)
Method 7:
DeleteAFrom#T AwhereId>any(SelectIdFrom#TwhereName=A.name)
Select * From#T

Build results:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1

(2 rows affected)
*/


--II, name the same ID retain the largest record:
Method 1:
DeleteAFrom#T Awhere Exists(Select 1 From#TwhereName=A.nameandId>a.ID)
Method 2:
DeleteAFrom#T ALeft Join(Select Max(ID) Id,nameFrom#TGroup ByName) bOnA.name=B.nameanda.ID=b.ID
whereb.IDIs Null
Method 3:
DeleteAFrom#T AwhereIdNot In(Select Max(ID)From#TwhereName=A.name)
Method 4 (Note: Available if ID is unique):
DeleteAFrom#T AwhereIdNot In(Select Max(ID)From#TGroup ByName)
Method 5:
DeleteAFrom#T Awhere(Select Count(1)From#TwhereName=A.nameandId>a.ID)>0
Method 6:
DeleteAFrom#T AwhereId<>(Select Top 1IdFrom#TwhereName=A.nameOrder ByIdDesc)
Method 7:
DeleteAFrom#T AwhereId<any(SelectIdFrom#TwhereName=A.name)
Select * From#T
/*
ID Name Memo
----------- ---- ----
3 A A3
5 B B2

(2 rows affected)
*/


--3, delete duplicate records when there is no size relationship, handle duplicate values
-->--> (Roy) generates test data
 
If Not object_id('Tempdb.. #T')Is Null
 Drop Table#T
Go
Create Table#T ([Num] Int,[Name] nvarchar(1))
Insert#T
Select 1N'A' Union All
Select 1N'A' Union All
Select 1N'A' Union All
Select 2N'B' Union All
Select 2N'B'
Go

Method 1:
If object_id('Tempdb.. #')Is Not Null
 Drop Table#
Select Distinct * Into#From#T--Exclude duplicate record result set generate temporary table #
Truncate Table#T--Empty table
Insert#TSelect * From#--Insert temporary table # into table #t

--View Results
Select * From#T

/*
Num Name
----------- ----
1 A
2 B

(2 rows affected)
*/

--Re-execute test data with Method 2
Method 2:
Alter Table#TAddIdInt Identity--New Identity column
Go
DeleteAFrom#T Awhere Exists(Select 1 From#TwhereNum=A.numandName=A.nameandId>a.ID)--Keep Only one record
Go
Alter Table#TDrop ColumnId--To delete an identity column

--View Results
Select * From#T

/*
Num Name
----------- ----
2 b
1 A

(2 rows affected)

*/

--Re-execute test data with Method 3
Method 3:
DeclareRoy_cursorCursorLocalFor
Select Count(1)-1, Num,nameFrom#TGroup ByNum,nameHaving Count(1)>1
Declare @con Int,@Num Int,@Name nvarchar(1)
OpenRoy_cursor
Fetch Next FromRoy_cursorInto @con,@Num,@Name
While @ @Fetch_status=0
Begin 
 Set  rowcount   @con ; 
  Delete  #T  where  Num  =  @Num   and  Name  =  @Name  
  set   rowcount   0 ; 
  fetch   next   from  roy_cursor  into   @con ,  @Num ,  @Name  
  end  
  close  roy_cursor 
  deallocate  roy_cursor 
 
 -- View results  
  Select   *   from  #T 
 /*  
 Num Name 
---------------
 1 A 
 2 B 
 
 (2 rows affected) 
  */