sql判断邮箱是否合法_力扣SQL经典例题解析

力扣（Leecode中文版）作为一个重要的编程题库，几乎是每位互联网技术岗求职者在面试前必须光顾的网站。作为一名数据分析师的求职者，你当然不能错过这个宝贵的资源了，在这篇文章中，我主要是将力扣中关于数据库（SQL）部分的经典题目（主要是简单和中档难度的题目）进行总结。如果你对MySQL的基础知识掌握的还不是很牢固，可以参考我的上一篇文章MySQL核心知识点总结。

题目一：组合两个表

表1：Person

表2：Address

编写一个SQL查询，满足条件：无论 Person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

解析：这道题目应该是非常简单的，仅仅考察两张表联结的单一知识点，答案（这里所有提供的答案并不是唯一的正确答案，如果读者还有其它更好的方法欢迎在评论区讨论，下同）如下：

select FirstName,LastName,City,State from Person left join Address on Address.PersonId=Person.PersonId;

注意：从题干的要求来看，应该要将 Person 表作为主表，将 Address 表作为副表，所以用的是左连接。

题目二：第二高的薪水

编写一个SQL查询，获取 Employee 表中第二高的薪水（Salary）.

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 Null。

答案：

select (select distinct Salary from Employee order by Salary desc limit 1,1) as SecondHighestSalary

解析：运用子查询，内部先将薪资排名第二（注意去重）的员工筛选出来，外面将选出的字段重新命名。为什么不直接在内部对字段进行重命名？避免出现取空的情况（假想一下，如果所有人薪资都一样，那么就不存在第二名，直接用内部语句选出的员工对应的薪资是空的，但是如果用子查询就会返回Null，这是这道题唯一的难点所在）。

题目三：第N高的薪水

编写一个SQL查询，获取 Employee 表中第 N 高的薪水（Salary）。

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200 。如果不存在第 n 高的薪水，那么查询应返回 null。

解析：这道题是上一道题的一个升级版本，其难点在于如何实现这个N的自由赋值，下面是一种正确的答案：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET n = N-1;
  RETURN (
      select (select distinct Salary from Employee order by Salary desc limit 1 offset n) as getNthHighestSalary);
END 

这里面需要重点理解 limit... offset... 的用法，其中 offset 是偏移量，跳过...行的意思，所以当你要去除第N个数据时，你需要跳过前面的 N-1 个数据。例外，想这种在SQL中设置函数的方式也值得注意。

题目四：分数排名

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

正确的答案如下：

select a.Score as score, (select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a order by score desc;

解析：这道题目的难点在于名次之间不能有‘间隔’。假设这个是我们班考试的成绩表，现在要计算自己的分数排名（分数相同，排名相同，名次之间不能有‘间隔’），那么你要做的就是数一下比自己分数高的不同分数的个数（内层查询干的事），然后将分数按照降序排列即可。

题目五：连续出现的数字

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

正确的答案：

select distinct l1.Num as ConsecutiveNums from Logs l1
left join Logs l2 on l2.Id = l1.Id+1
left join Logs l3 on l3.Id = l1.Id+2
where l1.Num = l2.Num and l1.Num = l3.Num

解析：这道题目是非常经典的面试题，在实际中也有比较大的应用价值。这里提供的答案思路很清晰，也很好理解： 连接3个表，保证连续的3个Id对应的 Num 值相等。

题目六：超过经理收入的员工

Employee 表包含所有的员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的Id 。

给定 Employee 表，编写一个SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，joe是唯一一个收入超过他的经理的员工。

正确答案：

select e1.Name as Employee from Employee e1
inner join Employee e2 on e1.ManagerId = e2.Id where e1.Salary > e2.Salary;

解析：这道题也不是很难，考察最基本的自连接知识。

题目七：查找重复的电子邮箱

编写一个SQL查询，查找 Person 表中所有重复的电子邮箱。

示例：

根据以上输入，你的查询应返回以下结果：

说明：所有电子邮箱都是小写字母。

正确答案：

select distinct p1.Email from Person p1
inner join Person p2
on p1.Email = p2.Email and p1.Id <> p2.Id

解析：

自连接，条件是 Id不同但是对应的邮箱地址相同，注意最后选出来的邮箱同样要去重。

题目八：从不订购的客户

某网站包含两个表， Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

Orders 表：

例如给定上述表格，你的查询应返回：

正确答案：

select Name as Customers from Customers cus where cus.Id not in
(select cus.Id from Customers cus inner join Orders ord on cus.Id = ord.CustomersId)

解析：答案的思路也比较简单，内层语句主要将 Customers 表和 Orders 表连接起来，并将有订单的用户名返回，外层就只用在所有用户中排除有过订单的用户，那么剩下的就是从没有订过单的用户。

题目九：部门工资最高的员工

Employee 表包含所有员工的信息，每个员工有其对应的 Id,salary 和 department Id。

Department 表包含公司所有部门的信息。

编写一个SQL查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max在IT部门有最高工资，Henry 在 Sales 部门有最高工资。

正确答案：

select d.Name as Department, e.Name as Employee, Salary from Employee e 
inner join Department d on e.DepartmentId = d.Id
where (DepartmentId,Salary) in 
(select DepartmentId,max(Salary) from Employee group by DepartmentId)

解析：答案中巧妙的地方在于 where 条件中 in 的用法，之前我们只是用于判断一个字段中是否包含某个元素，这里提示我们两个字段也可以用于in的查询中。

题目十：部门工资前三高的所有员工

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name , 工资 Salary 和部门编号 DepartmentId 。

Department 表包含公司所有部门的信息。

编写一个SQL查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

解释：

IT部门中，Max获得了最高的工资，Randy和Joe都拿到了第二高的工资，Will的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam的工资排第二。

正确答案：

select Department.Name as Department,e1.Name as Employee, Salary from Employee as e1
inner join
Department on e1.DepartmentId = Department.Id
where 3 > (select count(distinct e2.Salary) from Employee as e2 where e1.Salary < e2.Salary and e1.DepartmentId = e2.DepartmentId)
order by Department.Name,Salary desc;

解析：这道题目在上一题的基础上又增加了不少的难度，其中答案的 where 部分的功能是找出公司里前3高的薪水，以下举例说明：

当 e1 = e2 = [4,5,6,7,8]

e1.Salary = 4 , e2.Salary 可以取值[5,6,7,8],count(distinct e2.Salary) = 4

e1.Salary = 5, e2.Salary 可以取值[6,7,8],count(distinct e2.Salary) = 3

e1.Salary = 6, e2.Salary 可以取值[7,8], count(distinct e2.Salary) = 2

e1.Salary = 7, e2.Salary 可以取值[8], count(distinct e2.Salary) = 1

e1.Salary = 8, e2.Salary 可以取值[], count(distinct e2.Salary) = 0

最后 3>count(distinct e2.Salary),所以e1.Salary 可取值为[6,7,8], 即集合中前 3 高的薪水。然后再将Employee表和Department表联合起来。

题目十一：删除重复的电子邮箱

编写一个SQL查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。

例如，在运行你的查询语句之后，上面的 person 表应返回以下几行：

正确答案：

delete from Person where Id not in
(select Id from (select min(Id) Id,Email from Person group by Email) t)

解析：根据Email进行分组，将每组中除了Id最小的记录以外的其他记录删除

题目十二：上升的温度

给定一个 Weather 表，编写一个SQL查询，来查找与之前（昨天的）日期相比温度更高的所有日期的Id。

例如，根据上述给定的 Weather 表格，返回如下Id:

正确答案：

select b.Id from Weather as a,Weather as b where a.Temperature < b.Temperature and datediff(a.RecordDate,b.RecordDate) = -1;

解析：答案中的 datediff 用法值得注意，它是计算两个日期之间相差的天数，这里的-1 是指括号前面参数表示的时间比后面参数表示的时间要提前一天。另外，Mysql的查询中，使用到查询两日期之间相差多少天，多少周等的情况，可以使用mysql的内置函数，TimeStampDiff(间隔类型，前一个日期，后一个日期），其中间隔类型有 frac_second(毫秒)，second(秒)，minute(分钟),hour(小时),day(天)，week(星期), month(月)，quarter(季度),year(年);

select TimeStampDiff(day,'2019-09-08','2020-09-08');   # 计算两个日期之间的相差天数

题目十三：大的国家

这里有张 World 表

如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国。编写一个SQL查询，输出表中所有大国家的名称、人口和面积。

例如，根据上表，我们应该输出：

正确答案：

select name,population,area from World where area > 3000000
union
select name,population,area from World where population > 25000000;

解析：考察自连接的知识，比较简单。

题目十四：超过5名学生的课

有一个 courses 表，有：student(学生) 和 class(课程).

请列出所有超过或等于5名学生的课。例如，表：

应该输出：

Note：

学生在每个课中不应该被重复计算。

正确答案：

select class from courses group by class having count(distinct(student)) >= 5;

解析：此处一定要注意SQL语句的使用顺序，对分组后的字段再进行条件筛选时需用 having语句。SQL语句的执行顺序：

select...from...where...group by...having...order by...limit

题目十五：有趣的电影

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个LED显示板做电影推荐，上面公布着影评和相关电影的描述。作为该电影院的信息部主管，您需要编写一个SQL查询，找出所有影片描述为非boring（不无聊）的并且id为奇数的影片，结果请按等级rating排列。

例如，下表 cinema:

对于上面的例子，则正确的输出是为：

正确答案为：

select id,movie,description,rating from (select * from cinema where description != 'boring') as tenary where id%2 =1 order by rating desc;

解析：首先，将非boring电影给挑选出来，然后再从中挑选id为奇数的电影。

题目十六：换座位

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位id. 其中纵列的id是连续递增的，小美想改变相邻俩学生的座位。你能不能帮她写一个SQL query 来输出小美想要的结果呢？

示例：

假如数据输入的是上表，则输出结果如下：

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

select (case when mod(id,2) = 1 and id = sm.ma then id 
             when mod(id,2) = 1 and id <> sm.ma then id+1
             else id-1 end) id,student
             from seat,(seat max(id) as ma from seat) sm
            order by id;

解析：这个答案思路非常清晰，运用了控制流程函数，主要逻辑是，如果id为奇数且是最大值，那么id不变，如果id为奇数但不是最大值，那么将id加一，如果id为偶数，那么就将id减一，这样就实现了题目要求的换座位的效果。

题目十七：交换工资

给定一个salary表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的f和m值（例如，将所有f值更改为m，反之亦然）。要求只使用一个更新(Update)语句，并且没有中间的临时表。注意，您必只能写一个Update语句，请不要编写任何Select语句。

例如：

运行你所能编写的更新语句之后，将会得到以下表：

答案一：

update salary set sex = (case sex when 'm' then 'f' else 'm' end);

答案二：

update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

解析：答案一和答案二都是正确的，答案一用的是比较常规的控制流程函数。答案二比较巧妙，将编码转换和运算综合在一起，值得借鉴。

参考

题库 - 力扣 (LeetCode) 全球极客挚爱的技术成长平台​leetcode-cn.com

如果您想在数据分析的道路上走的更深，欢迎关注公众号“数据的奥秘”，让我们共同成长！