How can I Find Duplicated Rows in a Table?

最新推荐文章于 2024-10-18 13:02:57 发布

cnk45946

最新推荐文章于 2024-10-18 13:02:57 发布

阅读量116

点赞数

文章标签：数据库

此语句对mysql 4.0以后的版本生效.
SELECT name_first, name_last, COUNT(*) AS tally,
   FROM contacts
   GROUP BY name_first, name_last
   HAVING tally > 1;

How can I Find Duplicated Rows in a Table? [ID 1023312.1]

		Modified 17-AUG-2011 Type HOWTO Migrated ID 5280 Status PUBLISHED

Applies to:

MySQL Server - Version: 4.0 and later [Release: 4.0 and later ]
All Platforms
***Checked for relevance on 17-Aug-2011***

Goal

Tips for finding duplicated rows in a table.

Solution

Finding duplicate rows in a large table can be difficult. Duplicate rows could include a table containing two or more rows in which all of their columns have the same values. This could be in a table in which there is no key column. Or it could be that there is a key column and it's different, but all of the other columns match or perhaps just a few significant columns. How duplicate rows are identified is up to you. The problem is how to easily find duplicates based on your definition.

Solution

If there are too many rows in a table to efficiently inspect the table manually, you can find them with the COUNT() function. As an example, suppose you have a table containing the names and telephone numbers of sales contacts. Suppose further that there are duplicate entries for some contacts. To find duplicate rows in a table based on a couple of columns in particular, you can enter an SQL statement like the following:

SELECT name_first, name_last, COUNT(*) AS tally,
   FROM contacts
   GROUP BY name_first, name_last
   HAVING tally > 1;

This statement will list one row for each contact for which there is at least one duplicate entry (having the same first and last name) and give a tally of the number of entries for the contact. See Document 1023311.1 to learn how to to easily delete the duplicate entries.