SQL实现LeetCode(196.删除重复邮箱)

[LeetCode] 196.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  |
+----+------------------+

这道题让我们删除重复邮箱，那我们可以首先找出所有不重复的邮箱，然后取个反就是重复的邮箱，都删掉即可，那么我们如何找出所有不重复的邮箱呢，我们可以按照邮箱群组起来，然后用Min关键字挑出较小的，然后取补集删除即可：

解法一：

DELETE FROM Person WHERE Id NOT IN
(SELECT Id FROM (SELECT MIN(Id) Id FROM Person GROUP BY Email) p);

我们也可以使用内交让两个表以邮箱关联起来，然后把相同邮箱且Id大的删除掉，参见代码如下：

解法二：

DELETE p2 FROM Person p1 JOIN Person p2
ON p2.Email = p1.Email WHERE p2.Id > p1.Id;

我们也可以不用Join，而直接用where将两表关联起来也行：

解法三：

DELETE p2 FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p2.Id > p1.Id;

类似题目：

Duplicate Emails

参考资料：

https://leetcode.com/discuss/61176/simple-solution-using-a-self-join

https://leetcode.com/discuss/48403/my-answer-delete-duplicate-emails-with-double-nested-query

到此这篇关于SQL实现LeetCode(196.删除重复邮箱)的文章就介绍到这了,更多相关SQL实现删除重复邮箱内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • C++实现LeetCode(190.颠倒二进制位)

    [LeetCode] 190. Reverse Bits 颠倒二进制位 Reverse bits of a given 32 bits unsigned integer. Example 1: Input: 00000010100101000001111010011100 Output: 00111001011110000010100101000000 Explanation: The input binary string 00000010100101000001111010011100 re

  • C++实现LeetCode(188.买卖股票的最佳时间之四)

    [LeetCode] 188.Best Time to Buy and Sell Stock IV 买卖股票的最佳时间之四 Say you have an array for which the ith element is the price of a given stock on day i. Design an algorithm to find the maximum profit. You may complete at most k transactions. Note: You m

  • Pipes实现LeetCode(195.第十行)

    [LeetCode] 195.Tenth Line 第十行 How would you print just the 10th line of a file? For example, assume that file.txt has the following content: Line 1 Line 2 Line 3 Line 4 Line 5 Line 6 Line 7 Line 8 Line 9 Line 10 Your script should output the tenth li

  • Pipes实现LeetCode(193.验证电话号码)

    [LeetCode] 193.Valid Phone Numbers 验证电话号码 Given a text file file.txt that contains list of phone numbers (one per line), write a one liner bash script to print all valid phone numbers. You may assume that a valid phone number must appear in one of th

  • C++实现LeetCode(309.买股票的最佳时间含冷冻期)

    [LeetCode] 309.Best Time to Buy and Sell Stock with Cooldown 买股票的最佳时间含冷冻期 Say you have an array for which the ith element is the price of a given stock on day i. Design an algorithm to find the maximum profit. You may complete as many transactions as

  • Pipes实现LeetCode(192.单词频率)

    [LeetCode] 192.Word Frequency 单词频率 Write a bash script to calculate the frequency of each word in a text file words.txt. For simplicity sake, you may assume: words.txt contains only lowercase characters and space ' ' characters. Each word must consis

  • Pipes实现LeetCode(194.转置文件)

    [LeetCode] 194.Transpose File 转置文件 Given a text file file.txt, transpose its content. You may assume that each row has the same number of columns and each field is separated by the ' ' character. For example, if file.txt has the following content: na

  • C++实现LeetCode(191.位1的个数)

    [LeetCode] 191.Number of 1 Bits 位1的个数 Write a function that takes an unsigned integer and returns the number of '1' bits it has (also known as the Hamming weight). For example, the 32-bit integer '11' has binary representation 00000000000000000000000

  • SQL实现LeetCode(196.删除重复邮箱)

    [LeetCode] 196.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            | +----+------------

  • Mysql一些复杂的sql语句(查询与删除重复的行)

    1.查找重复的行 SELECT * FROM blog_user_relation a WHERE (a.account_instance_id,a.follow_account_instance_id) IN (SELECT account_instance_id,follow_account_instance_id FROM blog_user_relation GROUP BY account_instance_id, follow_account_instance_id HAVING C

  • SQL删除重复的电子邮箱力扣题目解答流程

    SQL删除重复的电子邮箱力扣题目解答流程

    目录 写在前面 SQL题目概述 解题思路 方法实现 代码测试 知识点小结 1.内连接innerjoin(join默认就是内连接) 2.左外连接leftjoin 3.右外连接rightjoin 4.全外连接fulljoin 写在前面 想要掌握好SQL,那少不了每天的练习与学习.接下来小梦会带领小伙伴们一起每天刷一道LeetCode-数据库(SQL)相关的题目,然后在文章后例举相关知识点帮助小伙伴们学习与巩固,更好的掌握SQL. ‍♂️ 小伙伴们如果在学习过程中有不明白的地方,欢迎评论区留言提问,小

  • SQL 查询和删除重复字段数据的方法

    例如: id           name         value 1               a                 pp 2               a                 pp 3               b                 iii 4               b                 pp 5               b                 pp 6               c           

  • oracle查询重复数据和删除重复记录示例分享

    一.查询某个字段重复 select * from User u where u.user_name in (select u.user_name from User u group by u.user_name having count(*) > 1) 二,删除表中某几个字段的重复 例:表中有条六条记录.   其中张三和王五   的记录有重复 TableA id customer PhoneNo 001 张三 777777 002 李四 444444 003 王五 555555 004 张三 7

  • mysql删除重复记录语句的方法

    例如: id name value 1 a pp 2 a pp 3 b iii 4 b pp 5 b pp 6 c pp 7 c pp 8 c iii id是主键 要求得到这样的结果 id name value 1 a pp 3 b iii 4 b pp 6 c pp 8 c iii 方法1 delete YourTable where [id] not in ( select max([id]) from YourTable group by (name + value)) 方法2 delet

  • SQL实现LeetCode(182.重复的邮箱)

    [LeetCode] 182.Duplicate Emails 重复的邮箱 Write a SQL query to find all duplicate emails in a table named Person. +----+---------+ | Id | Email   | +----+---------+ | 1  | a@b.com | | 2  | c@d.com | | 3  | a@b.com | +----+---------+ For example, your que

  • SQL重复记录查询 查询多个字段、多表查询、删除重复记录的方法

    SQL重复记录查询 1.查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 例二:  select * from testtable where numeber in (select number from people group by numbe

  • SQL对冗余数据的删除重复记录只保留单条的说明

    我们先看一下相关数据结构的知识. 在学习线性表的时候,曾有这样一个例题. 已知一个存储整数的顺序表La,试构造顺序表Lb,要求顺序表Lb中只包含顺序表La中所有值不相同的数据元素. 算法思路: 先把顺序表La的第一个元素付给顺序表Lb,然后从顺序表La的第2个元素起,每一个元素与顺序表Lb中的每一个元素进行比较,如果不相同,则把该元素附加到顺序表Lb的末尾. 复制代码 代码如下: public SeqList<int> Purge(SeqList<int> La) { SeqLis

  • 有用的SQL语句（删除重复记录，收缩日志）

    删除重复记录,将TABLE_NAME中的不重复记录保存到#TABLE_NAME中 select distinct * into #table_name from table_name delete from table_name select * into table_name from #table_name drop table #table_name 与此相关的是"select into"选项,可以在数据库属性 对话框中,勾起来此项,或者在Query Analyzer中执行 ex

随机推荐