SQL删除重复数据只保留一条|极客笔记

SQL删除重复数据只保留一条

简介

在数据库中，重复数据是常见的问题之一。当我们在数据表中插入大量重复数据时，数据库会占用更多的存储空间，导致查询效率降低。此外，在某些情况下，重复数据可能会导致数据的不一致性和错误计算结果。因此，我们有时需要从数据表中删除重复的数据，只保留一条。

本文将从以下几个方面，介绍SQL删除重复数据的方法。
1. 什么是重复数据？
2. 如何判断重复数据？
3. 删除重复数据的方法
4. 示例代码

什么是重复数据？

在数据库中，重复数据指的是在一个数据表的某一或多个列中存在相同行的数据。举个示例，假如有一个学生表，其中的姓名列包含了多个相同的姓名，这些相同的姓名即为重复数据。我们的目标是删除这些重复数据，只保留一条。

如何判断重复数据？

在SQL中，我们使用GROUP BY和HAVING子句来判断重复数据。GROUP BY用于将数据按照指定的列进行分组，而HAVING用于过滤出符合某些条件的数据。

具体而言，我们可以按照以下步骤来判断重复数据：
1. 使用GROUP BY将数据按照需要去重的列进行分组。
2. 使用COUNT函数统计每组中重复数据的数量。
3. 使用HAVING子句指定条件，找出重复数据的组。

举个示例，假设我们有一个名为students的学生表，包含了以下几列：id、name、age。我们可以按照姓名列进行分组，统计每组中重复数据的数量，并找出重复数据的组，示例代码如下：

SELECT name, COUNT(*) as count FROM students
GROUP BY name
HAVING COUNT(*) > 1;

运行结果可能如下所示：

name	count
Zhang	3
Wang	2

从上表可知，学生表中存在三个名为”Zhang”和两个名为”Wang”的重复数据。

删除重复数据的方法

在SQL中，我们可以使用多种方法删除重复数据，包括以下几种：使用DISTINCT关键字删除重复数据、使用ROW_NUMBER()函数删除重复数据、使用临时表删除重复数据。

使用DISTINCT关键字删除重复数据

DISTINCT关键字用于从查询结果中删除重复数据，仅保留一条。我们可以使用DISTINCT关键字配合DELETE语句来删除重复数据，示例代码如下：

DELETE FROM students
WHERE id NOT IN (
    SELECT MIN(id)
    FROM students
    GROUP BY name
);

运行以上代码后，我们将删除学生表中除了每组重复数据的最小id对应的行外的其他行。

使用ROW_NUMBER()函数删除重复数据

ROW_NUMBER()函数用于为查询结果的每一行分配一个唯一的序号。我们可以使用ROW_NUMBER()函数配合公用表表达式（CTE）来删除重复数据，示例代码如下：

WITH CTE AS (
    SELECT id, name, age, 
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
    FROM students
)
DELETE FROM CTE WHERE rn > 1;

运行以上代码后，我们将删除学生表中除了每组重复数据的第一行外的其他行。

使用临时表删除重复数据

另一种删除重复数据的方法是使用临时表。我们可以将去重后的数据插入到临时表中，然后删除原表的数据，最后将临时表中的数据重新插入到原表中，示例代码如下：

-- 创建临时表
CREATE TABLE #temp_students (
    id INT,
    name VARCHAR(50),
    age INT
);

-- 将去重后的数据插入到临时表中
INSERT INTO #temp_students (id, name, age)
SELECT MIN(id), name, age
FROM students
GROUP BY name;

-- 删除原表数据
DELETE FROM students;

-- 将临时表数据重新插入到原表中
INSERT INTO students (id, name, age)
SELECT id, name, age
FROM #temp_students;

-- 删除临时表
DROP TABLE #temp_students;

以上代码通过创建一个临时表，将去重后的数据插入到临时表中，然后删除原表中的数据，最后将临时表中的数据重新插入到原表中。这样我们就成功地删除了重复数据。

示例代码

假设我们有一个名为students的学生表，包含了以下几列：id、name、age。我们可以使用以下示例代码来删除目标表中的重复数据。

-- 创建学生表
CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    age INT
);

-- 向学生表中插入测试数据
INSERT INTO students (id, name, age)
VALUES
    (1, 'Zhang', 18),
    (2, 'Wang', 20),
    (3, 'Zhang', 21),
    (4, 'Zhang', 19),
    (5, 'Li', 22),
    (6, 'Wang', 19);

-- 查看学生表中的数据
SELECT * FROM students;

运行以上代码后，我们的学生表可能如下所示：

id	name	age
1	Zhang	18
2	Wang	20
3	Zhang	21
4	Zhang	19
5	Li	22
6	Wang	19

接下来，我们可以使用以下示例代码使用DISTINCT关键字删除重复数据：

DELETE FROM students
WHERE id NOT IN (
    SELECT MIN(id)
    FROM students
    GROUP BY name
);

-- 查看删除重复数据后的学生表
SELECT * FROM students;

运行以上代码后，学生表将变为：

id	name	age
1	Zhang	18
2	Wang	20
5	Li	22

使用以上方法，我们成功删除了重复数据，只保留了一条。

总结

本文详细介绍了SQL删除重复数据只保留一条的方法。我们首先了解了重复数据的概念，并学习了如何使用GROUP BY和HAVING子句来判断重复数据。接着，我们介绍了三种删除重复数据的方法，包括使用DISTINCT关键字、使用`ROW_NUMBER()函数和使用临时表的方法。最后，我们通过示例代码演示了如何使用这些方法来删除重复数据。

虽然我们可以使用以上方法删除重复数据，但在实际应用中需要谨慎操作，以免误删重要数据。在执行删除操作之前，建议先备份数据或进行事务控制，以便可以回滚操作。