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

MySQL刪除重複資料

相關文章