MySQL - A complete list of methods for querying duplicate records and deleting duplicate records

Find all records with duplicate titles:

SELECT * FROM t_info a WHERE ((SELECT COUNT(*) FROM t_info WHERE Title = a.Title) > 1) ORDER BY Title DESC

1. Find duplicate records

1. Find all duplicate records

Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)

2. Filter duplicate records (only display one )

Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title)

Note: The record with the largest ID is displayed here

2. Delete duplicate records

1. Delete all Duplicate records (use with caution)

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

2. Keep one record (this should be what most people need^_^)

Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)

Note: Keep the record with the largest ID here

3. Example

1. Find the redundant duplicate records in the table. Duplicate records are determined based on a single field (peopleId)

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

2. Delete redundant duplicate records in the table. Duplicate records are judged based on a single field (peopleId), leaving only the record with the smallest 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. Search for redundant duplicate records in the table. (Multiple fields)

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 duplicate records (multiple fields) in the table, leaving only the record with the smallest 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. Look up redundant records in the table Duplicate records (multiple fields), excluding the record with the smallest 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)

4. Supplement

There are more than two duplicate records, one is a completely duplicate record, that is, all fields The second one is the records where some key fields are repeated. For example, the Name field is repeated, but other fields may not be repeated or all of them can be ignored.
1. For the first type of duplication, it is easier to solve. Use

select distinct * from tableName

to get a result set without duplicate records.
If the table needs to delete duplicate records (retaining 1 duplicate record), you can delete it as follows

select distinct * into #Tmp from tableName  
drop table tableName  
select * into tableName from #Tmp  
drop table #Tmp

The reason for this duplication is poor table design. Add a unique index column that is Can be solved.
2. This type of duplication problem usually requires retaining the first record among the duplicate records. The operation method is as follows
Assume that the duplicate fields are Name and Address, and it is required to obtain the unique result set of these two fields

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 above is the content of MySQL - a complete method of querying duplicate records and deleting duplicate records. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!