leetcode sql题练习
目录
思路总结
/*
待更
*/
1.175. 组合两个表
题目
表1: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主键
表2: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主键
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State
解答
select p.FirstName,p.LastName,a.City,a.State
from
person p left join address a
on
p.personid=a.personid
/*
补充：
1.数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left jion时，on和where条件的区别如下：
- on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
- where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
2.where单独使用时，类似于隐式内连接，只会取两个表的公共部分。
*/
2.176. 第二高的薪水
题目
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
解答
--思路1-小于最大的“最大数”
select max(distinct Salary) SecondHighestSalary
from employee
where
salary<(select max(distinct salary) from employee)
--分析：
--1.对于第n大的问题，该方法不灵活。
--2.由于max会自动过滤到值为null的数据，同时如果输入都为null，则max输出也为null，因此可以保证无值的时候会输出null
--3.max是聚合函数，输入多个，输出一个
--思路2：limit x offset y
select ifNull(
(select distinct salary
from Employee
order by Salary Desc
limit 1,1),null
) as SecondHighestSalary;
--1.limit x ，y的含义：表示从第x数据开始，读取y个。数据库中数据的记录是从0开始的。
--2.执行顺序是：order by->distinct->limit,因此不能忘记distinct的操作，如果忘记了后面的limit的操作就失效了
3.177.第N高的薪水
题目
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
解答
-- 思路1：
CREATE FUNCTION gehHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT N, 1
);
END
#补充
-- limit x ，y的含义：表示从第x数据开始，读取y个。数据库中数据的记录是从0开始的。
-- 2.执行顺序是：order by->distinct->limit,因此不能忘记distinct的操作，如果忘记了后面的limit的操作就失效了
-- 思路2：窗口函数
4.181. 超过经理收入的员工
题目
Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
解答
select Name as Employee
from Employee as a
inner join b
on a. ManagerId=b.Id
and a.Salary>b.Salary
--a表当做经理，b表当做员工
4.182. 查找重复的电子邮箱
题目
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
示例：
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入，你的查询应返回以下结果：
+---------+
| Email |
+---------+
| a@b.com |
+---------+
解析
select a.Email as Email from Person a group by a.Email having count(a.Email)>=2;
-- 解析
-- 对于重复问题，第一时间要想到group by
5.183. 从不订购的客户
题目
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格，你的查询应返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
解析
--在hive里面，on只支持等值连接，无法直接!=，但是可以等值连接了以后用where筛选
select a.Name as Customers
from Customers a
left join Orders b
on a.Id=b.CustomerId
where b.Id is null
6.196. 删除重复的电子邮箱
题目
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
提示：
- 执行 SQL 之后，输出是整个
Person表。 - 使用
delete语句。
解析
-- 两张表自连接，把p1表中的大的那个删除了
delete p1
from person p1
inner join person p2
on p1.email=p2.email and p1.id>p2.id
浙公网安备 33010602011771号