Home > Database > Mysql Tutorial > body text

MySQL - Detailed explanation of methods to query/delete duplicate records

黄舟
Release: 2017-03-13 13:34:16
Original
1580 people have browsed it

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
Copy after login

1. Find duplicate records

1. Find all duplicate records

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

2. Filter duplicate records ( Only one is displayed)

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

Note: The record with the largest ID is displayed here

2.DeleteDuplicate records

1.Delete all Duplicate records (use with caution)

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

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)
Copy after login

Note: Keep the record with the largest ID here

3. Example

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

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

2. Delete the table Duplicate records in the table 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)
Copy after login

3. Lookup the extra duplicate records (multiple fields) in the table

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
Copy after login

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)
Copy after login

5. Find redundant duplicate records (multiple fields) in the table field), 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)
Copy after login

4. Supplement

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

select distinct * from tableName
Copy after login

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
Copy after login

The reason for this duplication is poor table design. Add unique Index column 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)
Copy after login

The above is the detailed content of MySQL - Detailed explanation of methods to query/delete duplicate records. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!