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

Question

我有一张棒球选手的 table (大约 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    |                |
+-----------+-------------+------+-----+---------+----------------+

但我认为有些球员已经加入了两次。如何查看特定名字、姓氏组合出现的次数？

Answer 1

这提供了重复的列表:

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

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

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