drop table if exists test ;
create table test(
id int primary key auto_increment,
name varchar(10)
);

insert into test(name) values('1');
insert into test(name) values('2');
insert into test(name) values('3');
insert into test(name) values('4');
insert into test(name) values('5');
insert into test(name) values('6');
insert into test(name) values('7');
insert into test(name) values('8');
insert into test(name) values('9');
insert into test(name) values('9');
insert into test(name) values('9');
commit;

在重复的三个数据中要保留一个，删除另外两个.
这个在Oracle里面简直不是事儿.
但是MySQL实现这个功能还是挺费劲.

方法一采用临时表过渡
create table test1 as select * from test group by name;
rename table test to tmp,test1 to test,tmp to test1;

方法二内联视图删除
delete t1 from test t1
inner join
(select name,min(id) minid from test group by name having count(*)>1) t2
on (t1.name=t2.name and t1.id!=t2.minid);

先找到有重复name的id(这部分的数据最终保留),然后自关联找到name相同而id不同的数据,这些就是需要删除的数据.

参考:
http://www.cnblogs.com/sunss/archive/2011/01/29/1947469.html

ITPub博客

MySQL删除重复数据

最新文章