MySQL 去重(4种方式,图文讲解)
一则或许对你有用的小广告
欢迎加入小哈的星球 ,你将获得:专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论
- 新项目:《从零手撸:仿小红书(微服务架构)》 正在持续爆肝中,基于
Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...
,点击查看项目介绍 ; - 《从零手撸:前后端分离博客项目(全栈开发)》 2 期已完结,演示链接: http://116.62.199.48/ ;
截止目前, 星球 内专栏累计输出 54w+ 字,讲解图 2476+ 张,还在持续爆肝中.. 后续还会上新更多项目,目标是将 Java 领域典型的项目都整一波,如秒杀系统, 在线商城, IM 即时通讯,权限管理,Spring Cloud Alibaba 微服务等等,已有 1900+ 小伙伴加入学习 ,欢迎点击围观
在数据库操作中,去重是一个比较常见的需求。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_name
和 last_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_name
和 last_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 IN
或 NOT 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_name
和 last_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_name
和 last_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
子句,可以灵活地对数据进行去重处理。在实际操作中,应根据具体需求选择合适的方法,以达到最佳的性能和效果。