Prerequisites:The same data is repeatedly written to the database, resulting in duplicate data with only the primary key ID, which now needs to be de-duplicated, preserving only one of the largest IDs in duplicate data
Ideas:1. Find the record with duplicate data, and take the largest ID value 2 in the duplicate data, delete the record that does not contain the maximum ID value in the record
implementation:DELETE from [Log_iis]. [dbo] . [iislog_table] WHERE ID not in(Select MAX(Id) from [iislog_table] Group by [RequestTime] ,[LogFileName] ,[Logrow] ,[Date] ,[TIME] ,[cIp] ,[csusername] ,[ssitename] ,[scomputername] ,[sIp] ,[SPort] ,[Csmethod] ,[Csuristem] ,[csuriquery] ,[scstatus] ,[scsubstatus] ,[scwin32status] ,[scbytes] ,[csbytes] ,[Timetaken] ,[csversion] ,[cshost] ,[csuseragent] ,[Cscookie] ,[csreferer] ,[sevent] ,[Sprocesstype] ,[susertime] ,[skerneltime] ,[spagefaults] ,[Stotalprocs] ,[Sactiveprocs] ,[Sstoppedprocs] having Count(*) > 1)
From for notes (Wiz)
Sqlserver_ Delete duplicate rows keep only one record