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
);
3.插⼊测试数据
insert into users values(0,'620616************','张三'),
(0,'620616************','张三'),
(0,'620626************','李四'),
(0,'620626************','李四'),
(0,'620622************','王五'),
(0,'620622************','王五'),
(0,'322235************','赵六');
可以多执⾏⼏次，⽣成较多重复数据。
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⾥，不能先select⼀个表的记录，再按此条件进⾏更新和删除同⼀个表的记录。
解决办法是，将select得到的结果，再通过中间表select⼀遍，这样就规避了错误，mysql删除重复的数据保留一条
这个问题只出现于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
意思是说：提⽰说每⼀个衍⽣出来的表，必须要有⾃⼰的别名！
执⾏⼦查询的时候，外层查询会将内层的查询当做⼀张表来处理，所以我们需要给内层的查询加上别名
继续更正：
给查询到的最⼤（或最⼩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了，如下图：
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
);
执⾏结果：
成功将重复的数据删除，只保留了最后⼀次增加的记录。同理也可以保留第⼀次添加的记录（即删除每个分组⾥⾯除最⼩id以外的其他条记录）
3.知识拓展⼀：更新数据
其他场景应⽤：要将⽤户表user_info⾥名字(name)为空字符串("")的⽤户的状态(status)改成"0"
update user_info set status='0' where user_id in (select user_id from user_info where name='')
同样报了如下错误：
You can't specify target table ‘user_info' for update in FROM clause
因为在MYSQL⾥，不能先select⼀个表的记录，再按此条件进⾏更新和删除同⼀个表的记录，解决办法是，将select得到的结果，再通过中间表select⼀遍，这样就规避了错误。
以下两种均可
update user_info set status='0' where user_id in
(select user_id from (select user_id from user_info where name = '') t1);
下⾯这种也可，细微差别，别名可带as可不带，t1.user_id 直接和内层的user_id对应也可以。
update user_info set status='0' where user_id in
(select t1.user_id from (select user_id from user_info where name='') as t1);
3.1 分步骤解析
（1）将以下查询结果作为中间表：
select user_id from user_info where name='';
（2）再查询⼀遍中间表作为结果集：
select user_id from (select user_id from user_info where name='') as t;
（3）更新数据
update user_info set status='0' where user_id in
(select user_id from (select user_id from user_info where name='') as t1);
4.拓展练习：删除重复数据
编写⼀个 SQL 查询，来删除 Person 表中所有重复的电⼦邮箱，重复的邮箱⾥只保留 Id 最⼩的那个。
+----+------------------+
| Id | Email      |
+----+------------------+
| 1 | john@example |
| 2 | bob@example |
| 3 | john@example |
+----+------------------+
Id 是这个表的主键。
例如，在运⾏你的查询语句之后，上⾯的 Person 表应返回以下⼏⾏:
+----+------------------+
| Id | Email      |
+----+------------------+
| 1 | john@example |
| 2 | bob@example |
+----+------------------+
解答⼀：
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;
总结
到此这篇关于mysql数据库删除重复数据的⽅法只保留⼀条的⽂章就介绍到这了，更多相关mysql删除重复数据内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。