The query method of duplicate Fields in MySQL large Table

This article mainly explains "the query method of duplicate fields in MySQL table". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the query method of repeating fields in MySQL large table.

SELECT min (`id`), `name` FROM `table` GROUP BY `name`

But you can't get an id value that says there are duplicate fields. (only the smallest id value is obtained)

It is also easy to query which fields are duplicated.

SELECT `name`, count (`name`) as count FROM `table` GROUP BY `name` HAVING count (`name`) > 1 ORDER BY count DESC

But to query the id value of a repeating field at one time, you must use a subquery, so use the following statement to implement the MySQL large table repeating field query.

SELECT `id`, `name` FROM `table` WHERE `name` in (SELECT `name` FROM `table` GROUP BY `name` HAVING count (`name`) > 1)

But this statement is so inefficient in that it feels like mysql doesn't generate temporary tables for subqueries.

So use to create a temporary table first.

Create table `tmptable` as (SELECT `name` FROM `table` GROUP BY `name` HAVING count (`name`) > 1)

Then use the multi-table join query

SELECT a.`id`, a.`name` FROM `table`a, `tmptable`t WHERE a.`name` = t.`name`

As a result, the results came out soon.

Repeat with distinct

SELECT distinct a.`id`, a.`name` FROM `table`a, `tmptable`t WHERE a.`name` = t.`name`. At this point, I believe you have a deeper understanding of "query method for duplicate fields in MySQL large table". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!