Query repeated records in the MySQL table

Welcome to the Linux community forum, and interact with 2 million technical staff to enter the duplicate records in the MySQL query table (1) 1. Find the redundant duplicate records in the table. The duplicate records are based on a single field (peopleId) select * frompeoplewherepeopleIdin (selectpeopleIdfrompeoplegroupbypeopleIdhavi

Welcome to the Linux community forum, and interact with 2 million technical staff> go to the MySQL query table for Repeated Records (1) 1. Find redundant duplicate records in the table, repeat records are used to determine the select * from people where peopleId in (select peopleId from people group by peopleId havi) based on a single field.

Welcome to the Linux community forum and interact with 2 million technicians>

Query repeated records in the MySQL table

(1)

1. Search for 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 unnecessary duplicate records in the Table. duplicate records are determined based on a single field (peopleId) and only one record is left.

Delete from people

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

And min (id) not in (select id from people group by peopleId having count (peopleId)> 1)

3. Search for redundant duplicate records in the table (multiple fields)

Select * from vitae

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

Delete 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)

5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid

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)

(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 integer

Declare cur_rows cursor local for select Main field, count (*) from table name group by main field having count (*)>; 1

Open cur_rows

Fetch cur_rows into @ id, @ max

While @ fetch_status = 0

Begin

Select @ max = @ max-1

Set rowcount @ max

Delete from table name where primary field = @ id

Fetch cur_rows into @ id, @ max

End

Close cur_rows

Set rowcount 0