如何在多列上检查mysql表中的重复项

I have a table of baseball players(all 1000 or so), with fields:

我有一个棒球运动员表（全部1000左右），有以下字段：

mysql> describe person;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(30) | NO   |     | NULL    |                |
| lastname  | varchar(30) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

But I think there are some players that have gotten added in twice. How can I go through and check for how many occurrences of a particular firstname, lastname combo?

但我认为有些球员已经两次加入。我怎样才能查看特定名字，姓氏组合的出现次数？

6 个解决方案

#1

This provides the list of duplicates:

这提供了重复列表：

SELECT firstname, lastname, COUNT(*) 
FROM person 
GROUP BY firstname, lastname 
HAVING COUNT(*) > 1;

If you want to see the counts for every row remove the having clause:

如果要查看每行的计数，请删除having子句：

SELECT firstname, lastname, COUNT(*) 
FROM person 
GROUP BY firstname, lastname;

#2

SELECT firstname, lastname, count(id) count
  FROM person
 WHERE firstname = ?
   AND lasttname = ?
 GROUP BY firstname, lastname

#3

For a list sorted by decreasing value of the number of copies:

对于按减少的份数值排序的列表：

SELECT firstname, lastname, COUNT(*) AS n
  FROM person
 GROUP BY firstname, lastname
 ORDER BY n DESC
 HAVING n > 1

The HAVING clause is the key part - it's necessary to filter the results after the GROUP BY clause, since a WHERE clause filters out rows before they're grouped.

HAVING子句是关键部分 - 必须在GROUP BY子句之后过滤结果，因为WHERE子句在对行进行分组之前对其进行过滤。

#4

To get id's of duplicate names as well as names do:

要获取重复名称和名称的ID，请执行以下操作：

SELECT p1.id, p1.firstname, p1,lastname FROM person p1
INNER JOIN person p2 ON (p1.firstname = p2.firstname 
                         AND p1.lastname = p1.lastname 
                         AND p1.id <> p2.id);

#5

If you simply want to erase all the duplicate, you could do a temporary table and fill it up with all youre data except the duplicate and them re-update youre primary table.

如果您只想删除所有重复项，则可以执行临时表并使用除重复项之外的所有数据填充它们并重新更新您的主表。

The query to select the data with duplicate would be this

选择具有重复数据的查询将是这样的

 SELECT DISTINCT firstname, lastname FROM table

To get the complete list of data in you're table

获取您表中的完整数据列表

SELECT firstname, lastname, COUNT(*) AS n
  FROM person
 GROUP BY firstname, lastname
 ORDER BY lastname DESC
 HAVING n > 1

With this last query you'll get a the list of data sorted by lastname Alphabeticly.

使用最后一个查询，您将获得按姓氏字节排序的数据列表。

#6

To find duplicate records (ex: to find login name and password combination of duplicate records) in a table use the below query;

要在表中查找重复记录（例如：查找重复记录的登录名和密码组合），请使用以下查询;

SELECT em.* FROM employee_master AS em JOIN 
 (SELECT emp.login, emp.password, COUNT(*) 
  FROM employee_master emp 
  WHERE emp.login != '' AND emp.password != '' 
  GROUP BY emp.login, emp.PASSWORD
  HAVING COUNT(*) > 1
 ) AS dl 
WHERE em.login =  dl.login AND em.password = dl.password;

#1