删除重复数据面试题

person 含有id email 两个字段 删除重复的email 数据 保留重复数据中id 最小的那一条

创建 person 表：

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `email` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

添加数据：

insert into person values 
(1,'liusf1@163.com'),
(2,'liusf2@163.com'),
(3,'liusf1@163.com'),
(4,'liusf3@163.com'),
(5,'liusf3@163.com');

错误语句一：

DELETE 
FROM
	person 
WHERE
	id NOT IN ( SELECT min( id ) AS id FROM person GROUP BY email );

异常信息：You can’t specify target table ‘person’ for update in FROM clause
原因是：不能在FROM子句中指定要更新的目标表
解决办法：多套层查询，作为临时表，让Mysql认为你更新的和查询的不是同一张表就OK了

错误语句二：

DELETE 
FROM
	person 
WHERE
	id NOT IN ( SELECT id FROM ( SELECT min( id ) AS id FROM person GROUP BY email ) )

异常信息： Every derived table must have its own alias
原因是：每个临时表都要有别名

最总解决语句：

DELETE 
FROM
	person 
WHERE
	id NOT IN ( SELECT tem.id FROM ( SELECT min( id ) AS id FROM person GROUP BY email )tem )