MySQL 查询所有重复记录

在数据库中，当表中有多条记录具有相同的值时，这些记录被称为重复记录。重复记录可能会导致数据冗余和查询结果不准确等问题。因此，我们需要查找并删除这些重复记录。本文将介绍如何使用MySQL查询所有重复记录，并提供一些示例。

阅读更多：MySQL 教程

创建重复记录的示例表格

为了演示如何查询重复记录，我们需要先创建一个具有重复记录的表。以下是一个名为Student的示例表，其中的记录具有相同的名字（Name）和出生日期（DOB）：

CREATE TABLE Student (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    DOB DATE NOT NULL,
    Grade INT NOT NULL
);

INSERT INTO Student(Name, DOB, Grade)
VALUES ('Tom', '1990-01-01', 90),
       ('Mary', '1990-01-02', 88),
       ('Tom', '1990-01-01', 95),
       ('Jerry', '1990-01-03', 92),
       ('Tom', '1990-01-04', 85);

以上代码中，我们创建了一个名为Student的表，并向其中插入了5条记录。其中，前三条记录具有相同的Name和DOB，它们被认为是重复记录。现在我们需要查询这些重复记录。

查询所有重复记录

要查询所有重复记录，我们需要使用GROUP BY和HAVING子句。GROUP BY子句将结果集按指定的列分组，HAVING子句过滤掉不符合条件的分组。以下是查询所有重复记录的SQL语句：

SELECT Name, DOB, COUNT(*) AS cnt
FROM Student
GROUP BY Name, DOB
HAVING cnt > 1;

以上代码中，我们首先将Student表按Name和DOB分组，然后使用聚合函数COUNT(*)计算出每个分组的记录数，并将该值命名为cnt。最后，HAVING子句过滤掉不符合条件（即cnt > 1）的分组，只保留具有重复记录的分组。

执行以上SQL语句，我们可以得到以下结果：

+------+------------+-----+
| Name | DOB        | cnt |
+------+------------+-----+
| Tom  | 1990-01-01 |   2 |
+------+------------+-----+

以上结果表明，名为Tom且出生日期为1990-01-01的记录具有2个重复的记录。

查询重复记录的数量

如果我们只需要知道有多少条记录是重复的，可以使用以下SQL语句：

SELECT COUNT(*) AS cnt
FROM (
    SELECT Name, DOB
    FROM Student
    GROUP BY Name, DOB
    HAVING COUNT(*) > 1
) t;

以上代码中，我们首先使用子查询查询出具有重复记录的Name和DOB，然后根据该子查询的结果计算出重复记录的数量，并将其命名为cnt。

执行以上SQL语句，我们可以得到以下结果：

+-----+
| cnt |
+-----+
|   1 |
+-----+

以上结果表明，Student表中有1个重复的记录分组。

删除重复记录

如果我们想要删除重复记录，可以使用DELETE和子查询。以下是一个删除Student表中所有重复记录的示例SQL语句：

DELETE FROM Student
WHERE ID NOT IN (
    SELECT MIN(ID)
    FROM Student
    GROUP BY Name, DOB
);

以上代码中，我们首先使用子查询查询出每个具有重复记录的分组中具有最小ID的记录，然后将该子查询的结果作为条件，删除除这些记录以外的所有记录。

执行以上SQL语句后，我们可以验证Student表中的重复记录已被删除：

SELECT * FROM Student;

+----+-------+------------+-------+
| ID | Name  | DOB        | Grade |
+----+-------+------------+-------+
|  1 | Tom   | 1990-01-01 |    90 |
|  2 | Mary  | 1990-01-02 |    88 |
|  4 | Jerry | 1990-01-03 |    92 |
|  5 | Tom   | 1990-01-04 |    85 |
+----+-------+------------+-------+

以上结果表明，Student表中的重复记录已被成功删除。

总结

在本文中，我们介绍了如何使用MySQL查询所有重复记录，并提供了一些示例。要查询重复记录，我们需要使用GROUP BY和HAVING子句，它们可以将结果集按指定的列分组，然后计算每个分组的记录数并过滤不符合条件的分组。如果要删除重复记录，则可以使用DELETE和子查询，它们可以找到每个具有重复记录的分组中具有最小ID的记录，并删除除这些记录以外的所有记录。