SQL deduplication (seven types) and SQL deduplication (seven types)
For more information, see the code:
if not object_id('Tempdb..#T') is null drop table #TGoCreate table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))Insert #Tselect 1,N'A',N'A1' union allselect 2,N'A',N'A2' union allselect 3,N'A',N'A3' union allselect 4,N'B',N'B1' union allselect 5,N'B',N'B2'Go
-- Record with the minimum ID for I and Name (1, 2, 3 is recommended), and the minimum record is retained
Method 1:
delete a from #T a where exists(select 1 from #T where Name=a.Name and ID<a.ID)
Method 2:
delete a from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null
Method 3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)
Method 4 (Note: It is available when ID is unique ):
delete a from #T a where ID not in(select min(ID)from #T group by Name)
Method 5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0
Method 6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)
Method 7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)select * from #T
The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!