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 query should return the following for the above table:
+---------+
| Email |
+---------+
| a@b.com |
+---------+
Note: All emails are in lowercase.
这道题让我们求重复的邮箱,那么最直接的方法就是用Group by...Having Count(*)...的固定搭配来做,代码如下:
解法一:
SELECT Email FROM Person GROUP BY Email HAVING COUNT(*) > 1;
我们还可以用内交来做,用Email来内交两个表,然后返回Id不同的行,则说明两个不同的人使用了相同的邮箱:
解法二:
SELECT DISTINCT p1.Email FROM Person p1 JOIN Person p2 ON p1.Email = p2.Email WHERE p1.Id <> p2.Id;
参考资料:
https://leetcode.com/discuss/53206/a-solution-using-a-group-by-and-another-one-using-a-self-join
到此这篇关于SQL实现LeetCode(182.重复的邮箱)的文章就介绍到这了,更多相关SQL实现重复的邮箱内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!
相关推荐
-
C++实现LeetCode(173.二叉搜索树迭代器)
[LeetCode] 173.Binary Search Tree Iterator 二叉搜索树迭代器 Implement an iterator over a binary search tree (BST). Your iterator will be initialized with the root node of a BST. Calling next() will return the next smallest number in the BST. Note: next() and
-
SQL实现LeetCode(178.分数排行)
[LeetCode] 178.Rank Scores 分数排行 Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, th
-
C++实现LeetCode(179.最大组合数)
[LeetCode] 179. Largest Number 最大组合数 Given a list of non negative integers, arrange them such that they form the largest number. Example 1: Input: [10,2] Output: "210" Example 2: Input: [3,30,34,5,9] Output: "9534330" Note: The result
-
SQL实现LeetCode(177.第N高薪水)
[LeetCode] 177.Nth Highest Salary 第N高薪水 Write a SQL query to get the nth highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ For example, given the ab
-
SQL实现LeetCode(181.员工挣得比经理多)
[LeetCode] 181.Employees Earning More Than Their Managers 员工挣得比经理多 The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id. +----+-------+--------+-----------+ | Id | Na
-
SQL实现LeetCode(176.第二高薪水)
[LeetCode] 176.Second Highest Salary 第二高薪水 Write a SQL query to get the second highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ For example, given
-
SQL实现LeetCode(175.联合两表)
[LeetCode] 175.Combine Two Tables 联合两表 Table: Person +-------------+---------+ | Column Name | Type | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId is the
-
SQL实现LeetCode(180.连续的数字)
[LeetCode] 180.Consecutive Numbers 连续的数字 Write a SQL query to find all numbers that appear at least three times consecutively. +----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 |
-
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实现LeetCode(183.从未下单订购的顾客)
[LeetCode] 183.Customers Who Never Order 从未下单订购的顾客 Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything. Table: Customers. +----+-------+ | Id | Name |
-
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 | +----+------------
-
SQL语句实现删除重复记录并只保留一条
复制代码 代码如下: delete WeiBoTopics where Id in(select max(Id) from WeiBoTopics group by WeiBoId,Title having COUNT(*) > 1); SQL:删除重复数据,只保留一条用SQL语句,删除掉重复项只保留一条在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢 1.查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 复制代码 代码如下: select * fr
-
SQL实现LeetCode(185.系里前三高薪水)
[LeetCode] 185.Department Top Three Salaries 系里前三高薪水 The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +--
随机推荐
- oracle中if/else的三种实现方式详解
- BAT加密工具 EncryBat 非编译型bat批处理加密方案与代码
- Windows下自动备份MongoDB的批处理脚本
- Python中的二叉树查找算法模块使用指南
- Python生成随机数组的方法小结
- CI框架的安全性分析
- 用PHP实现ODBC数据分页显示一例
- Linux下MySQL 5.5.8 源码编译安装记录分享
- SQL分页查询存储过程代码分享
- 不重启Mysql修改root密码的方法
- Python常用内置函数总结
- Javascript中的方法链(Method Chaining)介绍
- 用vbs实现zip功能的脚本
- 基于jQuery实现网页打印功能
- Python实现通过文件路径获取文件hash值的方法
- 关于Oracle listener日志解析利器的使用方法
- cmd中MySQL中文数据乱码问题解决方法
- Android ViewPager 的使用总结
- android 实现类似微信缓存和即时更新好友头像示例
- Java编译器用maven打war包出错解决办法