20200723：mysql冗余数据删除

/*
主题： 冗余数据删除
解决思路：
01. 分组查询出不重复数据的id
02. 删除id 不在 01 的id 的记录
*/

– 准备数据
create table test20200723(
id int not null auto increment primary key,
sno varchar(50) not null comment ‘学号’,
name varchar(50) not null comment ‘姓名’,
age varchar(50) not null comment ‘年龄’
)engine=InnoDB defautl charset=utf8;

insert into test20200723(sno,name,age) values(‘2020072301’,‘张三’,‘17’);
insert into test20200723(sno,name,age) values(‘2020072302’,‘张二’,‘18’);
insert into test20200723(sno,name,age) values(‘2020072302’,‘张二’,‘18’);
insert into test20200723(sno,name,age) values(‘2020072303’,‘李四’,‘19’);
insert into test20200723(sno,name,age) values(‘2020072303’,‘李四’,‘19’);
insert into test20200723(sno,name,age) values(‘2020072303’,‘李四’,‘19’);
insert into test20200723(sno,name,age) values(‘2020072306’,‘占哥’,‘20’);

– 逻辑实现
select min(id) from test.test20200723 group by sno,name,age;
delete from test.test20200723 where id not in (select min(id) from test.test20200723 group by sno,name,age); – 报错: Table ‘test20200723’ is specified twice, both as a target for ‘DELETE’ and as a separate source for data
delete from test.test20200723 where id not in (select * from (select min(id) from test.test20200723 group by sno,name,age) A);
select * from test.test20200723