ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How can I Find Duplicated Rows in a Table?

How can I Find Duplicated Rows in a Table?

原创 Linux操作系统 作者:ilsyx 时间:2011-08-18 11:35:38 0 删除 编辑
此语句对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博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-705238/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-12

  • 博文量
    195
  • 访问量
    597156