LeetcodSQL196. Delete Duplicate Emails

原题链接

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 
    );
上一篇:hustoj搭建教程


下一篇:[LeetCode] 929. Unique Email Addresses_Easy tag: Hash