测试表结构

CREATE TABLE `testdeleterepetition` (

`id` int(3) default NULL,

`name` varchar(32) default NULL,

`hobby` varchar(32) default NULL,

`job` varchar(32) default NULL,

`cc` varchar(2) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `testdeleterepetition2` (

`id` int(3) default NULL,

`extend` varchar(32) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

表testdeleterepetition中的数据

insert into `testdeleterepetition` (`id`,`name`,`hobby`,`job`,`cc`) values('1','steve','11','22','33');

insert into `testdeleterepetition` (`id`,`cc`) values('2','ali','33','44');

insert into `testdeleterepetition` (`id`,`cc`) values('3',`cc`) values('4','cs','12','32','43');

insert into `testdeleterepetition` (`id`,`cc`) values('5',`cc`) values('6',`cc`) values('7','aa','fe','dv');

表testdeleterepetition2中的数据

insert into `testdeleterepetition2` (`id`,`extend`) values('1','1');

insert into `testdeleterepetition2` (`id`,`extend`) values('2',`extend`) values('3',`extend`) values('4','2');

insert into `testdeleterepetition2` (`id`,`extend`) values('5',`extend`) values('6',`extend`) values('7','2');删除testdeleterepetition中name、hobby、job、cc字段重复的列，保留id最小的列

DELETE FROM testdeleterepetition

WHERE id NOT IN (SELECT t.minid FROM (SELECT MIN(id) minid FROM `testdeleterepetition`

GROUP BY NAME,hobby,job) t);删除testdeleterepetition表中的重复数据，保留最小id，且和testdeleterepetition2表通过id字段关联，取testdeleterepetition2表extend字段为1为限制条件

DELETE testdeleterepetition a

FROM testdeleterepetition a

INNER JOIN testdeleterepetition2 b

ON a.`id`=b.`id`

WHERE a.id NOT IN (SELECT t.minid FROM (SELECT MIN(id) minid FROM `testdeleterepetition`

GROUP BY NAME,job) t)

AND b.extend=1;