Solution
只保留相同 Email 中 Id 最小的那一个,然后删除其它的。
连接查询:
DELETE p1
FROM
Person p1,
Person p2
WHERE
p1.Email = p2.Email
AND p1.Id > p2.Id
子查询:
DELETE
FROM
Person
WHERE
id NOT IN (
SELECT id
FROM (
SELECT min( id ) AS id
FROM Person
GROUP BY email
) AS m
);
应该注意的是上述解法额外嵌套了一个 SELECT 语句,如果不这么做,会出现错误:You can’t specify target table ‘Person’ for update in FROM clause。以下演示了这种错误解法。
DELETE
FROM
Person
WHERE
id NOT IN (
SELECT min( id ) AS id
FROM Person
GROUP BY email
);