mysql删除一张表中的重复数据

DROP TABLE IF EXISTS `imooc_goddess`;
CREATE TABLE `imooc_goddess` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(30) NOT NULL,
`sex` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`email` varchar(30) DEFAULT NULL,
`mobile` varchar(11) DEFAULT NULL,
`create_user` varchar(30) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`update_user` varchar(30) DEFAULT NULL,
`update_date` date DEFAULT NULL,
`isdel` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

 

INSERT INTO `imooc_goddess` VALUES ('2', '小彭', null, '23', null, null, null, null, null, null, null, null);
INSERT INTO `imooc_goddess` VALUES ('6', '胖子', '0', '78', '2016-10-03', 'hongming@qq.com', '1211555599', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('7', '小溪', '0', '34', '2016-10-03', 'hongming@qq.com', '1211555599', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('9', '小霞', null, '23', '1990-09-09', 'xiaoxia@qq.com', '232445455', null, '2016-10-03', null, '2016-10-03', null);INSERT INTO `imooc_goddess` VALUES ('10', 'hh', '1', '23', '1990-09-09', 'jkjfskf', '12323', 'Admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('121', '平', '1', '23', '2998-04-09', 'jjjj@ww.com', '1323', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('121', '航母', '1', '23', '2333-09-09', 'jkksjkjf', '1232', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('232', '胖纸', '1', '23', '1991-09-09', 'jjijijij', '1323244', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('343', '校长', '1', '18', '1998-09-09', 'jkjijij@qq.com', '112323424', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('111', '航母', '1', '23', '2333-09-09', 'jkksjkjf', '1232', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('222', '胖纸', '1', '23', '1991-09-09', 'jjijijij', '1323244', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('333', '校长', '1', '18', '1998-09-09', 'jkjijij@qq.com', '112323424', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');

 

DELETE FROM imooc_goddess 
where (email,user_name,mobile) in
#找出 email,user_name,mobile 字段重复的数据

( select a.email,a.user_name,a.mobile from (select email,user_name,mobile from imooc_goddess GROUP BY email,user_name,mobile HAVING count(id)>1) a)
and id not in 
# 保留重复数据中id 最小的一行不删除。
( SELECT b.ID from (select min(id) ID from imooc_goddess GROUP BY email ,user_name,mobile HAVING count(id)>1) b);