Repeated records in the MySQL Query table _ MySQL

Duplicate records in the MySQL Query table: bitsCN.com

Query repeated records in the MySQL table

(1)

1. search for redundant duplicate records in the table. Repeat Records determine the select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) 2. delete unnecessary duplicate records in the table. duplicate records are determined based on a single field (peopleId, only one record is left: delete from peoplewhere peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) and min (id) not in (select id from people group by distinct leid having count (distinct Leid)> 1) 3. search for redundant duplicate records in the table (multiple fields) select * from vitae awhere (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) 4. delete redundant record (multiple fields) in the table ), delete from vitae awhere (. 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. search for redundant duplicate records (multiple fields) in the table, excluding the records with the smallest rowid select * from vitae awhere (. 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)

(2)

For example

There is A field "name" in table ",

The "name" value may be the same for different records,

Now, you need to query items with duplicate "name" values between records in the table;

Select Name,Count(*) From A Group By Name Having Count(*) > 1

If the gender is also the same, the statement is as follows:

Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

(3)

Method 1

Declare @ max integer, @ id integerdeclare cur_rows cursor local for select main field, count (*) from table name group by main field having count (*)>; 1 open cur_rowsfetch cur_rows into @ id, @ maxwhile @ fetch_status = 0 beginselect @ max = @ max-1 set rowcount @ maxdelete from table name where primary field = @ idfetch cur_rows into @ id, @ maxendclose cur_rowsset rowcount 0

BitsCN.com