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

问题描述

我有一张棒球选手表（全部为1000左右），栏位为：

  mysql>描述人
 + ----------- + ------------- + ------ + ----- + ------ --- + ---------------- + 
 | Field |类型|空|关键|默认|额外| 
 + ----------- + ------------- + ------ + ----- + ------ --- + ---------------- + 
 | id | int（11）| NO | PRI | NULL | auto_increment | 
 |名字| varchar（30）| NO | | NULL | | 
 |姓| varchar（30）| NO | | NULL | | 
 + ----------- + ------------- + ------ + ----- + ------ --- + ---------------- + 
  

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

解决方案

这里提供了重复的列表：

  SELECT firstname，lastname，COUNT（*）
 FROM person 
 GROUP BY firstname，lastname 
 HAVING COUNT（*）> 1; 
  

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

  SELECT firstname，lastname，COUNT（*）
 FROM person 
 GROUP BY firstname，lastname; 
  

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?

解决方案

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:

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

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