mysql 数据库超强删除重复数据语句

作者：袖梨 2022-11-14

月小升今天遇到的问题是students这个表有md这个字段重复。看看如何处理吧。

代码如下	复制代码
select * from students where md in (select md from students group by md having count(md) > 1) order by md

注明，这个被group的字段，请索引，否则很慢

代码如下	复制代码
delete from students where md in (select md from students group by md having count(md) > 1) and id not in (select min(id) from students group by md having count(md )>1)

这个语句在mysql下会报错

#1093 – You can’t specify target table ‘students’ for update in FROM clause

原因是好像mysql不准许我们进行联合删除内有条件语句指向自己的表。

策略是使用临时表，存储那些要删除的ID

代码如下

复制代码

create table tmp (id int);

insert into tmp (id) select id from students
where md in (select md from students group by md having count(md) > 1)
and id not in (select min(id) from students group by md having count(md )>1);

delete from students where id in (select id from tmp);

得出会被删除的数据

代码如下	复制代码
select * from students where md in (select md from students group by md having count(md) > 1) and id not in (select min(id) from students group by md having count(md )>1)

得出过滤后的数据，不删除的数据。如果不用删除，此sql语句可以用来显示唯一数据