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

淡淡的烟草味﹌ 2022-08-09 09:59 110阅读 0赞

# 题目 #

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

<table> 
 <thead> 
  <tr> 
   <th align="left">Id</th> 
   <th align="left">Email</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">john@example.com</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">bob@example.com</td> 
  </tr> 
  <tr> 
   <td align="left">3</td> 
   <td align="left">john@example.com</td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <thead> 
  <tr> 
   <th align="left">Id</th> 
   <th align="left">Email</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">john@example.com</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">bob@example.com</td> 
  </tr> 
 </tbody> 
</table>

题目来源：[https://leetcode.com/problems/delete-duplicate-emails/][https_leetcode.com_problems_delete-duplicate-emails]

# 代码 #

Email重复的话，保留ID最小的那条记录，其余的删除。

#方法一：
    # Write your MySQL query statement below
    delete from Person where Id not in ( select * from ( select MIN(Id) from Person group by Email ) as tb );
    #方法二：
    # Write your MySQL query statement below
    delete p1 from Person as p1, Person as p2 where p1.Email = p2.Email and p1.Id > p2.Id;

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

发表评论取消回复

表情：

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

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

相关阅读

相关 LeetCode - Easy - 196. Delete Duplicate Emails

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

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

相关 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 赞/ 98 阅读

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

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

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

相关【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 赞/ 111 阅读

相关 Remove Duplicates from Sorted Array ——解题报告

【题目】 Given a sorted array, remove the duplicates in place such that each element

梦里梦外;/ 2022年08月08日 06:51/ 0 赞/ 97 阅读

相关 leetcode 196. Delete Duplicate Emails

196. Delete Duplicate Emails Question Editorial Solution [My Submissions][] Tota

àì夳堔傛蜴生んèń/ 2022年07月19日 01:53/ 0 赞/ 7 阅读

相关 leetcode 182. Duplicate Emails

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

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

相关 Abap DELETE - duplicates

今天发现自已一直忽视了一个问题， DELETE - duplicates 删除内表重复记录语句是有条件限制的，必需是依据关键字或你定义的字段排序后才行。仔细看了一下它的文档

电玩女神/ 2022年06月18日 02:53/ 0 赞/ 125 阅读

相关 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 赞/ 128 阅读