How to query duplicate fields in mysql

In mysql, you can use the count() function to query repeated fields. This function can return the results of specified conditions. The syntax is "SELECT field value COUNT(*) as count FROM table name GROUP BY field value having count>1;".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to query duplicate fields in mysql

Example The accountinfo table data is as follows:

Scenario 1 Single field duplicate data search & deduplication

We need to find the same data in the single field account field in the above table.

The idea is briefly described in three steps:

The first step

To find duplicate data, the first thing we think of is that since it is a duplicate, then the number is greater than 1 Even if It's repetition. That's the count function.

Because what we want to check is a single field account, then we need to group according to the account field dimension. That is the group by function.

Then the mysql statement we wrote in the first step is:

 SELECT account ,COUNT(account) as count FROM accountinfo GROUP BY account;

The query results are as follows:

The second step

Yes, as we thought, the data with count greater than 1 are the data with accounts A and B.

Then we filter a little and only find the account of the data whose count is greater than 1.

The second step is to use having to splice the filter conditions, and the written mysql statement is:

 SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1;

The query results are as follows:

Three steps

The duplicate account data A and B have been found. Next, we only need to query other data with accounts A and B together.

That is to use the data found in the second step as the subquery condition and use the IN function.

The mysql statement written in the third step is:

 SELECT * FROM  accountinfo WHERE account IN
 (
 SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1
 );

The query results are as follows:

You can see that the duplicate data has been We filtered it out.

Recommended learning: mysql video tutorial

The above is the detailed content of How to query duplicate fields in mysql. For more information, please follow other related articles on the PHP Chinese website!