在MySQL表中计算重复记录？

您可以使用MySQL的if()函数来计算重复记录。语法如下 –

SELECT yourColumnName, COUNT(*) AS anyVariableName, IF (
  COUNT(*)>1,"Duplicate Records", "Not Duplicate records") as anyVariableName FROM yourTableName group by yourColumnName;

为了理解上面的语法，让我们创建一个表。创建表的查询语句如下 –

mysql> CREATE TABLE DuplicateRecords
  -> (
  -> Id int NOT NULL AUTO_INCREMENT,
  -> Name varchar(30),
  -> PRIMARY KEY(Id)
  -> );
Query OK, 0 rows affected (0.82 sec)

使用insert命令向表中插入一些记录。查询语句如下 –

mysql> insert into DuplicateRecords(Name) values('Carol');
Query OK, 1 row affected (0.81 sec)

mysql> insert into DuplicateRecords(Name) values('John');
Query OK, 1 row affected (0.17 sec)

mysql> insert into DuplicateRecords(Name) values('Sam');
Query OK, 1 row affected (0.19 sec)

mysql> insert into DuplicateRecords(Name) values('John');
Query OK, 1 row affected (0.17 sec)

mysql> insert into DuplicateRecords(Name) values('Sam');
Query OK, 1 row affected (0.11 sec)

mysql> insert into DuplicateRecords(Name) values('Sam');
Query OK, 1 row affected (0.20 sec)

mysql> insert into DuplicateRecords(Name) values('John');
Query OK, 1 row affected (0.12 sec)

mysql> insert into DuplicateRecords(Name) values('Carol');
Query OK, 1 row affected (0.14 sec)

mysql> insert into DuplicateRecords(Name) values('Carol');
Query OK, 1 row affected (0.10 sec)

mysql> insert into DuplicateRecords(Name) values('Mike');
Query OK, 1 row affected (0.14 sec)

使用select语句显示表中的所有记录。查询语句如下 –

mysql> select *from DuplicateRecords;

以下是输出 –

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Carol |
| 2  | John  |
| 3  | Sam   |
| 4  | John  |
| 5  | Sam   |
| 6  | Sam   |
| 7  | John  |
| 8  | Carol |
| 9  | Carol |
| 10 | Mike  |
+----+-------+
10 rows in set (0.00 sec)

下面是计算表中重复记录的查询语句 –

mysql> SELECT Name, COUNT(*) AS Repetition, IF (COUNT(*)>1,"Duplicate Records", "Not Duplicate records") as IsDuplicateRecordsOrNot
  -> from DuplicateRecords group by Name;

以下是输出结果 –

+-------+------------+-------------------------+
| Name  | Repetition | IsDuplicateRecordsOrNot |
+-------+------------+-------------------------+
| Carol |        3  | Duplicate Records       |
| John  |        3  | Duplicate Records       |
| Sam   |        3  | Duplicate Records       |
| Mike  |        1  | Not Duplicate records   |
+-------+------------+-------------------------+
4 rows in set (0.00 sec)

阅读更多：MySQL 教程