此语句对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.

References

http://dev.mysql.com/doc/refman/5.5/en/select.html
NOTE:1023311.1 - How can I delete duplicate rows?
http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_count

Show Related Information Related

Products

Oracle Database Products > MySQL > MySQL Server > MySQL Server

Keywords

DUPLICATE ENTRY; MYSQL

ITPub博客

How can I Find Duplicated Rows in a Table?

Applies to:

Goal

Solution

Solution

References

最新文章