Leetcode196 Delete Duplicate Emails

Posted on 2020-02-22 Edited on 2020-02-26 In 数据库 Valine:

Problem:

Intuition:

注意，第二个答案中AS m那里不能删除。否则会报Every derived table must have its own alias（别名）错误。

Solution:

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
    );