Shulou(Shulou.com)06/01 Report--

This article uses simple and easy-to-understand examples to introduce MySQL how to query and delete duplicate records, the code is very detailed, interested friends can refer to, I hope to help you.

Find all records with duplicate titles:

select title,count(*) as count from user_table group by title having count>1;SELECT * FROM t_info a WHERE ((SELECT COUNT(*) FROM t_info WHERE Title = a.Title) > 1) ORDER BY Title DESC

Find all duplicate records

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

2. Filter duplicate records (only one is displayed)

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

Note: The record with the largest ID is displayed here.

II. Delete duplicate records

1. Delete all duplicate records (with caution)

Delete Table Where Duplicate Fields In (Select Duplicate Fields From Table Group By Duplicate Fields Having Count(*)>1)

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

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

Note: The record with the largest ID is reserved here.

III. Examples

1. Look up redundant duplicate records in the table. Duplicate records are judged according to 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 according to a single field (peopleId), leaving only records 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. Find 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)

Delete redundant duplicate records (multiple fields) in the table, leaving only records 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. Find redundant duplicate records (multiple fields) in the table, 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)

There are more than two duplicate records. One is a completely duplicate record, that is, a record with duplicate fields. The other is a record with duplicate key fields. For example, the Name field is duplicate, while other fields are not necessarily duplicate or can be ignored.

1. For the first kind of repetition, it is easier to solve and use

select distinct * from tableName

You can get a result set without duplicate records.

If duplicate records need to be deleted from this table (duplicate records remain 1), you can delete them as follows

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

This duplication occurs due to poor table design, which can be solved by adding unique index columns.

2. This type of duplicate problem usually requires that the first record in the duplicate record be kept. The operation method is as follows

Assuming there are duplicate fields Name,Address, you need to get 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) About MySQL how to query and delete duplicate records to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.