MySQL 去重(4种方式,图文讲解)

更新时间 2024-06-12 12:32:16

一则或许对你有用的小广告

欢迎加入小哈的星球 ,你将获得:专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸:仿小红书(微服务架构)》 正在持续爆肝中,基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...点击查看项目介绍 ;
  • 《从零手撸:前后端分离博客项目(全栈开发)》 2 期已完结,演示链接: http://116.62.199.48/ ;

截止目前, 星球 内专栏累计输出 50w+ 字,讲解图 2200+ 张,还在持续爆肝中.. 后续还会上新更多项目,目标是将 Java 领域典型的项目都整一波,如秒杀系统, 在线商城, IM 即时通讯,权限管理,Spring Cloud Alibaba 微服务等等,已有 1700+ 小伙伴加入学习 ,欢迎点击围观

在数据库操作中,去重是一个比较常见的需求。MySQL 提供了多种方法来实现数据去重,包括使用 DISTINCT 关键字、窗口函数和 GROUP BY 等方法。本小节中,就将详细介绍一下这些方法,帮助你在不同场景下实现数据去重。

1. 使用 DISTINCT 关键字

DISTINCT 关键字用于在查询结果中去除重复的行。它可以应用于单列或多列查询。

语法

SELECT DISTINCT column1, column2, ...
FROM table_name;

示例

假设有一个名为 employees 的表,包含以下数据:

+----+-----------+----------+
| id | first_name| last_name|
+----+-----------+----------+
| 1  | John      | Doe      |
| 2  | Jane      | Doe      |
| 3  | John      | Smith    |
| 4  | John      | Doe      |
+----+-----------+----------+

如果要查询去除重复的 first_name,可以使用以下语句:

SELECT DISTINCT first_name FROM employees;

结果:

+-----------+
| first_name|
+-----------+
| John      |
| Jane      |
+-----------+

如果要查询去除 first_namelast_name 的组合重复,使用以下语句:

SELECT DISTINCT first_name, last_name FROM employees;

结果:

+-----------+----------+
| first_name| last_name|
+-----------+----------+
| John      | Doe      |
| Jane      | Doe      |
| John      | Smith    |
+-----------+----------+

2. 使用窗口函数去重

窗口函数可以用于复杂的去重需求,特别是需要保留特定列或需要根据某些条件进行去重时。

语法

使用 ROW_NUMBER() 窗口函数与子查询结合,可以实现去重操作。

SELECT column1, column2, ...
FROM (
    SELECT column1, column2, ..., 
           ROW_NUMBER() OVER (PARTITION BY column_to_deduplicate ORDER BY some_column) AS row_num
    FROM table_name
) subquery
WHERE row_num = 1;

示例

假设有一个名为 orders 的表,包含以下数据:

+----+------------+-----------+-------+
| id | order_date | customer  | total |
+----+------------+-----------+-------+
| 1  | 2023-01-01 | Alice     | 100   |
| 2  | 2023-01-02 | Bob       | 200   |
| 3  | 2023-01-02 | Alice     | 150   |
| 4  | 2023-01-03 | Alice     | 200   |
+----+------------+-----------+-------+

如果要去除重复的 customer,并保留每个客户最早的订单,可以使用以下语句:

SELECT id, order_date, customer, total
FROM (
    SELECT id, order_date, customer, total,
           ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date) AS row_num
    FROM orders
) subquery
WHERE row_num = 1;

结果:

+----+------------+-----------+-------+
| id | order_date | customer  | total |
+----+------------+-----------+-------+
| 1  | 2023-01-01 | Alice     | 100   |
| 2  | 2023-01-02 | Bob       | 200   |
+----+------------+-----------+-------+

3. 删除重复数据

有时需要从表中删除重复的行,只保留一行。可以使用自连接或子查询来实现这一点。

使用自连接删除重复数据

通过自连接,找到重复的行,并删除除一行外的所有重复行。

语法

DELETE t1 FROM table_name t1
INNER JOIN table_name t2 
WHERE 
    t1.id > t2.id AND 
    t1.column_to_deduplicate = t2.column_to_deduplicate;

示例

假设有一个名为 employees 的表,包含以下数据:

+----+-----------+----------+
| id | first_name| last_name|
+----+-----------+----------+
| 1  | John      | Doe      |
| 2  | Jane      | Doe      |
| 3  | John      | Smith    |
| 4  | John      | Doe      |
+----+-----------+----------+

要删除重复的 first_namelast_name,只保留 id 最小的一行,使用以下语句:

DELETE e1 FROM employees e1
INNER JOIN employees e2
WHERE 
    e1.id > e2.id AND 
    e1.first_name = e2.first_name AND 
    e1.last_name = e2.last_name;

执行后,表 employees 变为:

+----+-----------+----------+
| id | first_name| last_name|
+----+-----------+----------+
| 1  | John      | Doe      |
| 2  | Jane      | Doe      |
| 3  | John      | Smith    |
+----+-----------+----------+

使用子查询删除重复数据

通过子查询和 NOT INNOT EXISTS 语句,找到重复的行,并删除除一行外的所有重复行。

示例

DELETE FROM employees
WHERE id NOT IN (
    SELECT * FROM (
        SELECT MIN(id)
        FROM employees
        GROUP BY first_name, last_name
    ) AS temp
);

这段 SQL 语句使用子查询找到每组 first_namelast_name 中最小的 id,然后删除不在这些 id 列表中的所有行。

4. 使用 GROUP BY 去重

GROUP BY 子句用于将数据按一个或多个列进行分组,可以用于去重,但通常是与聚合函数一起使用。

语法

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...;

示例

假设有一个名为 employees 的表,包含以下数据:

+----+-----------+----------+
| id | first_name| last_name|
+----+-----------+----------+
| 1  | John      | Doe      |
| 2  | Jane      | Doe      |
| 3  | John      | Smith    |
| 4  | John      | Doe      |
+----+-----------+----------+

如果要去除重复的 first_namelast_name 组合,可以使用以下语句:

SELECT first_name, last_name
FROM employees
GROUP BY first_name, last_name;

结果:

+-----------+----------+
| first_name| last_name|
+-----------+----------+
| John      | Doe      |
| Jane      | Doe      |
| John      | Smith    |
+-----------+----------+

结论

MySQL 提供了多种方法来实现数据去重,每种方法适用于不同的场景。通过使用 DISTINCT 关键字、窗口函数、自连接或子查询以及 GROUP BY 子句,可以灵活地对数据进行去重处理。在实际操作中,应根据具体需求选择合适的方法,以达到最佳的性能和效果。