MySQL 如何检查mysql表中多列的重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6454805/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check for duplicates in mysql table over multiple columns
提问by cfrederich
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?
但我认为有些球员已经被加入了两次。我如何检查并检查特定名字、姓氏组合出现的次数？
回答by RC.
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:
如果您想查看每一行的计数，请删除 have 子句：
SELECT firstname, lastname, COUNT(*)
FROM person
GROUP BY firstname, lastname;
回答by manji
SELECT firstname, lastname, count(id) count
FROM person
WHERE firstname = ?
AND lasttname = ?
GROUP BY firstname, lastname
回答by Alnitak
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 HAVINGclause is the key part - it's necessary to filter the results afterthe GROUP BYclause, since a WHEREclause filters out rows beforethey're grouped.
该HAVING条款是关键的部分-这是必要的过滤结果后的GROUP BY条款，因为WHERE条款过滤掉行前他们分组。
回答by Johan
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);
回答by PRacicot
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.
如果您只是想删除所有重复项，您可以创建一个临时表并用您的所有re data except the duplicate and them re-update you主表填充它。
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.
使用最后一个查询，您将获得按姓氏字母顺序排序的数据列表。
回答by Naresh Chennuri
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;
