LeetCode - Easy - 196. Delete Duplicate Emails

喜欢ヅ旅行 2022-12-30 10:34 117阅读 0赞

## Topic ##

*  SQL

## Description ##

[https://leetcode.com/problems/delete-duplicate-emails/][https_leetcode.com_problems_delete-duplicate-emails]

Write a SQL query to **delete** all duplicate email entries in a table named `Person`, keeping only unique emails based on its smallest **Id**.

+----+------------------+
    | Id | Email            |
    +----+------------------+
    | 1  | john@example.com |
    | 2  | bob@example.com  |
    | 3  | john@example.com |
    +----+------------------+

Id is the primary key column for this table.  
For example, after running your query, the above `Person` table should have the following rows:

+----+------------------+
    | Id | Email            |
    +----+------------------+
    | 1  | john@example.com |
    | 2  | bob@example.com  |
    +----+------------------+

**Note**:

Your output is the whole `Person` table after executing your sql. Use `delete` statement.

## Analysis ##

略

## Submission ##

DELETE p FROM Person p, Person q WHERE p.Id>q.Id AND q.Email=p.Email
    
    ------------------------------------------------------------------------
    -- this clause doesn't word in mysql
    -- DELETE FROM Person WHERE id NOT IN(SELECT min(id) AS id FROM Person GROUP BY email)
    --
    -- you will be noted " You can't specify target table 'Person' for update in FROM clause ",
    -- The solution is using a middle table with select clause:
    
    DELETE FROM Person WHERE id NOT IN(
        SELECT t.id FROM (
            SELECT min(id) AS id FROM Person GROUP BY email
        ) t
    )

## Test ##

略

[https_leetcode.com_problems_delete-duplicate-emails]: https://leetcode.com/problems/delete-duplicate-emails/

发表评论取消回复

表情：

评论列表（有 0 条评论，117人围观）

还没有评论，来说两句吧...

相关阅读

相关 LeetCode 196. Delete Duplicate Emails (删除重复的电子邮箱)

题目标签：　　题目给了我们一个 email 的表格，让我们删除重复的。　　建立Person p1，Person p2，当email 相同时，而且 p1 id 要大于 p

落日映苍穹つ/ 2023年08月17日 16:45/ 0 赞/ 48 阅读

相关 LeetCode - Easy - 219. Contains Duplicate II

Topic Array Hash Table Sliding Window Description [https://leetcode.com/

女爷i/ 2022年12月31日 05:29/ 0 赞/ 135 阅读

相关 LeetCode - Easy - 217. Contains Duplicate

Topic Array Hash Table Description [https://leetcode.com/problems/contains-

桃扇骨/ 2022年12月31日 03:22/ 0 赞/ 115 阅读

相关 LeetCode - Easy - 196. Delete Duplicate Emails

Topic SQL Description [https://leetcode.com/problems/delete-duplicate-emails/]

喜欢ヅ旅行/ 2022年12月30日 10:34/ 0 赞/ 118 阅读

相关 LeetCode - Easy - 182. Duplicate Emails

Topic Easy Description [https://leetcode.com/problems/duplicate-emails/][https

红太狼/ 2022年12月29日 12:57/ 0 赞/ 123 阅读

相关【leetcode Database】196. Delete Duplicate Emails

题目： Write a SQL query to delete all duplicate email entries in a table named `Person`,

╰+哭是因爲堅強的太久メ/ 2022年09月25日 01:16/ 0 赞/ 94 阅读

相关【MySQL】【leetcode】 Duplicate Emails解题报告

题目 Write a SQL query to find all duplicate emails in a table named Person. \+—-+———+

向右看齐/ 2022年08月09日 09:59/ 0 赞/ 105 阅读

相关【MySQL】【leetcode】 Delete Duplicate Emails解题报告

题目 Write a SQL query to delete all duplicate email entries in a table named Person, k

淡淡的烟草味﹌/ 2022年08月09日 09:59/ 0 赞/ 108 阅读

相关 leetcode 182. Duplicate Emails

182. Duplicate Emails Question Editorial Solution [My Submissions][] Total Accep

秒速五厘米/ 2022年07月19日 01:50/ 0 赞/ 100 阅读

相关 196. Delete Duplicate Emails

Write a SQL query to delete all duplicate email entries in a table named Person, keeping

浅浅的花香味﹌/ 2022年06月10日 14:42/ 0 赞/ 125 阅读