如何检查mysql表中多列的重复项 [英] How to check for duplicates in mysql table over multiple columns

问题描述

我有一张棒球运动员表(全部 1000 名左右)，有字段:

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

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?

推荐答案

这提供了重复的列表:

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

如果您想查看每一行的计数，请删除 have 子句:

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

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

这篇关于如何检查mysql表中多列的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！