Delete duplicate records in sqlserver and save one of them according to the conditions

The implementation code is as follows:

The code is as follows:

Copy code

SELECT * FROM [extract]. [dbo]. [tbTradeFullinfoGet] WHERE tid IN (SELECT tid FROM [extract]. [dbo]. [tbTradeFullinfoGet] Group by tid Having count (tid)> 1) order by tid desc Select distinct tid, * into # aaa from [extract]. [dbo]. [tbTradeFullinfoGet] Truncate table [extract]. [dbo]. [tbTradeFullinfoGet] Insert [extract]. [dbo]. [tbTradeFullinfoGet] select * from # aaa Delete from [extract]. [dbo]. [tbTradeFullinfoGet] Where tid in (select tid from [extract]. [dbo]. [tbTradeFullinfoGet] group by tid having count (tid)> 1) And intime not in (select max (intime) from [extract]. [dbo]. [tbTradeFullinfoGet] group by tid having count (tid)> 1) Select .*, ROW_NUMBER () over (partition by a. tid order by a. intime desc) as rows_id Into # test_a From [extract]. [dbo]. [tbTradeFullinfoGet] Delete from # test_a Where rows_id <> '1' Alter table # test_a drop column rows_id Drop table [extract]. [dbo]. [tbTradeFullinfoGet] Select * Into [extract]. [dbo]. [tbTradeFullinfoGet] From ## test_a

The code function is to delete repeated records in tbTradeFullinfoGet and only save the maximum intime record in the table!

Other SQL statements used to delete repeated records

1. SQL Server searches for redundant duplicate records in the table. Duplicate records are determined based on a single field (peopleId).

The code is as follows:	Copy code
Select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

2. Delete unnecessary duplicate records in the table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid

The code is as follows:	Copy code
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. Search for redundant duplicate records in the table (multiple fields)

The code is as follows:	Copy code
Select * from vitae a where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)

4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid

The code is as follows:	Copy code
Delete from vitae a where (. peopleId,. 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. SQL Server searches for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid

The code is as follows:	Copy code
Select * from vitae 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)

Additional knowledge

There are two repeated records. One is a completely repeated record, that is, records with all fields being repeated, and the other is Records with duplicate key fields, such as duplicate Name fields, other fields are not necessarily repeated or can be ignored.
1. For the first type of repetition, it is easier to solve.

The code is as follows:	Copy code
Select distinct * from tableName

You can get the result set without repeated records.
If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:

The code is as follows:	Copy code
Select distinct * into # Tmp from tableName Drop table tableName Select * into tableName from # Tmp Drop table # Tmp

The reason for this repetition is that the table design is not weekly. You can add a unique index column.
2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:
Assume that the duplicate fields are Name and Address. You must obtain the unique result set of the two fields.

The code is as follows:	Copy code
Select identity (int, 1, 1) as autoID, * into # Tmp from tableName Select min (autoID) as autoID into # Tmp2 from # Tmp group by Name, autoID Select * from # Tmp where autoID in (select autoID from # tmp2)

The last select command gets the result set with no duplicate Name and Address (but an autoID field is added, which can be omitted in the select clause when writing)
3. Some key fields are repeated and the record contains IDs.
The first method deletes all duplicate records at a time (only records with the smallest ID in the repeat are retained ).

The code is as follows:	Copy code
Delete from table where id not in (select min (id) from table group by name)

The second method deletes only one record with the largest duplicate ID at a time.

The code is as follows:	Copy code
Delete from table where id in (select max (id) from table group by name having count (*) & gt; 1)