Shulou(Shulou.com)05/31 Report--

This article is about how mysql queries for duplicate fields. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In mysql, you can query duplicate fields using the count () function, which returns the result of a specified condition with the syntax "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 by mysql

The sample accountinfo table data is as follows:

Scenario A single field duplicate data lookup & de-duplication

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

The train of thought is summarized in three steps:

The first step

To find duplicate data, the first thing that comes to mind is that since it is duplicated, a quantity greater than 1 is considered duplicated. That's the count function.

Because we are troubleshooting a single field account, we need to group according to the account field dimension. That's the group by function.

So 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:

Step two

Yes, as we thought, data with count greater than 1 is data with account An and B.

So let's filter a little bit and find out only the account of the data whose count is greater than 1.

The second step is to use the having splicing filter to write the mysql statement as follows:

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

The query results are as follows:

Step three

The duplicate account data An and B have been found, and then we just need to query all the other data with account An and B.

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:

We can see that the duplicate data have been screened out by us.

Thank you for reading! This is the end of this article on "how to query duplicate fields in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.