Mysql-删除表中重复的行，只保留一行

最新推荐文章于 2024-04-16 08:30:17 发布

markliwei1

最新推荐文章于 2024-04-16 08:30:17 发布

阅读量2.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： Mysql

本文链接：https://blog.youkuaiyun.com/makeliwei1/article/details/81240789

Mysql 专栏收录该内容

13 篇文章

订阅专栏

该博客介绍了如何在MySQL中删除表中重复的行，只保留一行。首先，通过`GROUP BY`子句找出不同的`lastname`，然后选择每个组的最小`id`，接着使用中间表策略删除非最小`id`的行，以确保只保留每个`lastname`的唯一行。此外，还讨论了如何将表的内容复制到另一张新表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、stu 表结构如图

+----+-----------+----------+---------+-------------+
| id | firstname | lastname | address | phone       |
+----+-----------+----------+---------+-------------+
|  4 | Wu        | BaiTao   | Hunan   | 12345678912 |
|  5 | Zhou      | BaiTao   | Hunan   | 12345678912 |
|  6 | Sun       | BaiTao   | Hunan   | 12345678912 |
|  7 | Li        | BaiTao   | Hunan   | 12345678912 |
|  8 | Liu       | BaiTao   | Hunan   | 12345678912 |
| 11 | Li        | Mazi     | Hunan   | 12345678912 |
| 12 | Zhang     | Mazi     | Hunan   | 12345678912 |
| 13 | Zheng     | BaiTao   | Hunan   | 12345678912 |
| 14 | Wang      | BaiTao   | Hunan   | 12345678912 |
| 15 | Zeng      | BaiTao   | Hunan   | 12345678912 |
| 16 | Qiu       | BaiTao   | Hunan   | 12345678912 |
| 17 | Dan       | BaiTao   | Hunan   | 12345678912 |
| 18 | Hua       | BaiTao   | Hunan   | 12345678912 |
| 19 | Mao       | BaiTao   | Hunan   | 12345678912 |
| 22 | zou       | baitao   | Hunan   | 13212345632 |
+----+-----------+----------+---------+-------------+

二、任务要求：删除lastname中重复的行

第一步、使用group by子句找出所有不同的lastname字段

mysql>select lastname from stu group by lastname having count(*)>1;

输出结果

+----------+
| lastname |
+----------+
| BaiTao   |
| Feng     |
| Gao      |
| Mazi     |
| Tao      |
+----------+

第二步，找出出现这些字段出现的最小的id

select min(id) from stu group by lastname;

输出结果

+---------+
| min(id) |
+---------+
|       4 |
|      20 |
|      10 |
|      11 |
|       9 |
+---------+

第三步：删除所有的其他行除了那些出现在第二步中的id

delete from stu where id not in(select min(id) from stu group by lastname);

出现下面的错误，

You can't specify target table 'stu' for update in FROM clause

在网上找到结果说，我们可以将select出来的结果作为一张中间表，然后从这个中间表里面去删除

delete from stu where id not in
(select s.id from (select min(id) as id from stu s group by lastname) s
);

输出结果

+----+-----------+----------+---------+-------------+
| id | firstname | lastname | address | phone       |
+----+-----------+----------+---------+-------------+
|  4 | Wu        | BaiTao   | Hunan   | 12345678912 |
|  9 | Wang      | Tao      | Hunan   | 12345678912 |
| 10 | Zhao      | Gao      | Hunan   | 12345678912 |
| 11 | Li        | Mazi     | Hunan   | 12345678912 |
| 20 | Li        | Feng     | GuangXi | 12345678998 |
+----+-----------+----------+---------+-------------+