mysql数据库删除重复的数据保留一条

1.问题引入

假设一个场景,一张用户表,包含3个字段。id,identity_id,name。现在身份证号identity_id和姓名name有很多重复的数据,需要删除只保留一条有效数据。

2.模拟环境

  • 1.登入mysql数据库,创建一个单独的测试数据库mysql_exercise
create database mysql_exercise charset utf8;
  • 2.创建用户表users
create table users(
                    id int auto_increment primary key,
                    identity_id varchar(20),
                    name varchar(20) not null
                   );

mysql数据库删除重复的数据保留一条
  • 3.插入测试数据
insert into users values(0,'620616199409206512','张三'),
                        (0,'620616199409206512','张三'),
                        (0,'62062619930920651X','李四'),
                        (0,'62062619930920651X','李四'),
                        (0,'620622199101206211','王五'),
                        (0,'620622199101206211','王五'),
                        (0,'322235199909116233','赵六');

可以多执行几次,生成较多重复数据。

mysql数据库删除重复的数据保留一条
  • 4.解决思路
    (1)根据身份证号和name进行分组;
    (2)取出分组后的最大id(或最小id);
    (3)删除除最大(或最小)id以外的其他字段;
  • 5.第一次尝试(失败!!!)
delete from users where id not in (select max(id) from users group by identity_id,name);

报错:

1093 (HY000): You can't specify target table 'users' for update in FROM clause

mysql数据库删除重复的数据保留一条

因为在MYSQL里,不能先select一个表的记录,再按此条件进行更新和删除同一个表的记录。
解决办法是,将select得到的结果,再通过中间表select一遍,这样就规避了错误,
这个问题只出现于mysql,mssql和oracle不会出现此问题。

所以我们可以先将括号里面的sql语句先拿出来,先查到最大(或最小)id。

select max_id from (select max(id) as max_id from users group by identity_id,name);

接着,又报错了!!

ERROR 1248 (42000): Every derived table must have its own alias

意思是说:提示说每一个衍生出来的表,必须要有自己的别名!

执行子查询的时候,外层查询会将内层的查询当做一张表来处理,所以我们需要给内层的查询加上别名

mysql数据库删除重复的数据保留一条

继续更正:
给查询到的最大(或最小id)结果当做一张新的表,起别名 t,并查询 t.mix_id

select t.max_id from (select max(id) as max_id from users group by identity_id,name) as t;

可以成功查到最大(或最小)id了,如下图:

mysql数据库删除重复的数据保留一条
  • 6.第二次尝试(成功!!!)
delete from users where id not in (
        select t.max_id from
        (select max(id) as max_id from users group by identity_id,name) as t
        );

执行结果:

mysql数据库删除重复的数据保留一条

成功将重复的数据删除,只保留了最后一次增加的记录。同理也可以保留第一次添加的记录(即删除每个分组里面除最小id以外的其他条记录)

3.拓展练习:删除重复数据

编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
  • 解答一:
delete from Person where Id not in (
    select t.min_id from (
        select min(Id) as min_id from Person group by Email
        ) as t
    );
  • 解答二:
delete p1 from
    Person as p1,Person as p2
        where p1.Email=p2.Email and p1.Id > p2.Id;

参考文章:

https://blog.csdn.net/qq_38923792/article/details/95240733

Original: https://www.cnblogs.com/xiadongqing/p/15972797.html
Author: 夏冬青
Title: mysql数据库删除重复的数据保留一条

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/8186/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

发表回复

登录后才能评论
免费咨询
免费咨询
扫码关注
扫码关注
联系站长

站长Johngo!

大数据和算法重度研究者!

持续产出大数据、算法、LeetCode干货,以及业界好资源!

2022012703491714

微信来撩,免费咨询:xiaozhu_tec

分享本页
返回顶部