排版好有颜色的文章在公众号“八哥的成长心路札记”上有,微信号是bager1912。
以下题目均来自力扣(LeetCode)官网和其他网站,仅用作数据库爱好者学习交流,严禁进行商业及任何非法用途。
5.24知晓英国首相梅姨辞职了,世界局势复杂,别说大环境不好,世界环境都不好,在位的实权派女领导人也就只剩默克尔了,人家快65岁了,还在拼,我们更应该沉下心来,踏实沉淀自己,一起努力!
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 FirstName,LastName,City,State
from Person a left join Address b
on a.PersonId=b.PersonId;
176. 第二高的薪水
编写一个 SQL 查询,获取 Employee
表中第二高的薪水(Salary) 。
+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+
例如上述 Employee
表,SQL查询应该返回 200
作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null
。
+---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
select min(salary) "SecondHighestSalary"
from (select salary,dense_rank()over(order by salary desc) rn
from Employee
)
where rn=2;
这个地方我觉得min没有好像也行,不影响输出空值,但是去掉后,leetcode判定是错误的,有点奇怪。
177. 第N高的薪水
编写一个 SQL 查询,获取 Employee
表中第 n 高的薪水(Salary)。
+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+
例如上述 Employee
表,n = 2 时,应返回第二高的薪水 200
。如果不存在第 n 高的薪水,那么查询应返回 null
。
+------------------------+ | getNthHighestSalary(2) | +------------------------+ | 200 | +------------------------+
CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
/* Write your PL/SQL query statement below */
select Salary into result
from (select Salary,rownum as rn
from (select DISTINCT Salary
from Employee
order by Salary desc))
where rn = N;
RETURN result;
END;
这个嵌套了三层select
178. 分数排名
编写一个 SQL 查询来实现分数排名。如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
+----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+
例如,根据上述给定的 Scores
表,你的查询应该返回(按分数从高到低排列):
+-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+
select score "Score",dense_rank() over(order by score desc) "Rank"
from Scores;
180. 连续出现的数字
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
例如,给定上面的 Logs
表, 1
是唯一连续出现至少三次的数字。
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+
select distinct a.Num "ConsecutiveNums"
from Logs a,Logs b,Logs c
where a.Id=b.Id-1 and a.Id=c.Id-2 and a.Num=b.Num and a.Num=c.Num;
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 a.Name "Employee"
from Employee a,Employee b
where a.ManagerId = b.Id and a.Salary > b.Salary;
182. 查找重复的电子邮箱
编写一个 SQL 查询,查找 Person
表中所有重复的电子邮箱。
示例:
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
根据以上输入,你的查询应返回以下结果:
+---------+ | Email | +---------+ | a@b.com | +---------+
说明:所有电子邮箱都是小写字母。
select Email "Email"
from Person
group by Email
having count(Email) > 1;
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 | +-----------+
select Name "Customers"
from Customers
where Id not in
(select CustomerId
from Orders)
order by Name;
184. 部门工资最高的员工
Employee
表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | +----+-------+--------+--------------+
Department
表包含公司所有部门的信息。
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+
select b.Name "Department",a.Name "Employee",Salary "Salary"
from Employee a,Department b
where DepartmentId = b.Id and (Salary,DepartmentId) in (select max(Salary),DepartmentId
from Employee
group by DepartmentId);
先在员工信息表里按部门分组找出最高工资的员工,再连接部门表,显示要输出的信息。
185. 部门工资前三高的员工
Employee
表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id 。
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | +----+-------+--------+--------------+
Department
表包含公司所有部门的信息。
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
编写一个 SQL 查询,找出每个部门工资前三高的员工。例如,根据上述给定的表格,查询结果应返回:
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+
select d.name "Department",s.name "Employee",s.salary "Salary"
from (select departmentid,name,salary
from (select departmentid,name,salary,dense_rank() over (partition by departmentid order by salary desc) rn
from employee)
where rn < 4) s,department d
where s.departmentid = d.id;
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 | +----+------------------+
delete d1
from Person d1,Person d2
where d1.Email = d2.Email and d1.Id > d2.Id;
把d1的记录删除
197. 上升的温度
给定一个 Weather
表,编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。
+---------+------------------+------------------+ | Id(INT) | RecordDate(DATE) | Temperature(INT) | +---------+------------------+------------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +---------+------------------+------------------+
例如,根据上述给定的 Weather
表格,返回如下 Id:
+----+ | Id | +----+ | 2 | | 4 | +----+
select w1.Id "Id"
from Weather w1,Weather w2
where w1.RecordDate = w2.RecordDate + 1 and w1.Temperature > w2.Temperature;
262. 行程和用户
Trips
表中存所有出租车的行程信息。每段行程有唯一键 Id,Client_Id 和 Driver_Id 是 Users
表中 Users_Id 的外键。Status 是枚举类型,枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
+----+-----------+-----------+---------+--------------------+----------+ | Id | Client_Id | Driver_Id | City_Id | Status |Request_at| +----+-----------+-----------+---------+--------------------+----------+ | 1 | 1 | 10 | 1 | completed |2013-10-01| | 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01| | 3 | 3 | 12 | 6 | completed |2013-10-01| | 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01| | 5 | 1 | 10 | 1 | completed |2013-10-02| | 6 | 2 | 11 | 6 | completed |2013-10-02| | 7 | 3 | 12 | 6 | completed |2013-10-02| | 8 | 2 | 12 | 12 | completed |2013-10-03| | 9 | 3 | 10 | 12 | completed |2013-10-03| | 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03| +----+-----------+-----------+---------+--------------------+----------+
Users
表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止,Role 则是一个表示(‘client’, ‘driver’, ‘partner’)的枚举类型。
+----------+--------+--------+ | Users_Id | Banned | Role | +----------+--------+--------+ | 1 | No | client | | 2 | Yes | client | | 3 | No | client | | 4 | No | client | | 10 | No | driver | | 11 | No | driver | | 12 | No | driver | | 13 | No | driver | +----------+--------+--------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表,你的 SQL 语句应返回如下结果,取消率(Cancellation Rate)保留两位小数。
+------------+-------------------+ | Day | Cancellation Rate | +------------+-------------------+ | 2013-10-01 | 0.33 | | 2013-10-02 | 0.00 | | 2013-10-03 | 0.50 | +------------+-------------------+
select b.Request_at "Day",round(NVL(a.cancel,0)/b.total,2) "Cancellation Rate"
from (select Request_at,count(Status) cancel
from Trips t1
where Status != 'completed' and (select Banned
from Users
where Users_Id = t1.Client_Id) = 'No' and (select Banned
from Users
where Users_id = t1.Driver_Id)='No'
group by Request_at) a --a有一行是空的
full join
(select Request_at,count(Status) total
from Trips t1
where (select Banned
from Users
where Users_Id = t1.Client_Id) = 'No' and (select Banned
from Users
where Users_id = t1.Driver_Id)='No'
group by Request_at) b
on a.Request_at = b.Request_at
where b.Request_at between '2013-10-01' and '2013-10-03'
order by b.Request_at;
这道题得好好揣摩下,非禁止的用户有可能是乘客有可能是车主,力扣上有些只考虑乘客的也判定为对的,但感觉不够全面,于是结合评论中的代码理解,本来是想查询a.Request_at的,但其实a查询中有一行是空的,会导致最终结果不显示0取消率的那一行。所以只能查询b.Request_at。
569. 员工薪水中位数
Employee
表包含所有员工。Employee
表有三列:员工Id,公司名和薪水。
+-----+------------+--------+ |Id | Company | Salary | +-----+------------+--------+ |1 | A | 2341 | |2 | A | 341 | |3 | A | 15 | |4 | A | 15314 | |5 | A | 451 | |6 | A | 513 | |7 | B | 15 | |8 | B | 13 | |9 | B | 1154 | |10 | B | 1345 | |11 | B | 1221 | |12 | B | 234 | |13 | C | 2345 | |14 | C | 2645 | |15 | C | 2645 | |16 | C | 2652 | |17 | C | 65 | +-----+------------+--------+
请编写SQL查询来查找每个公司的薪水中位数。挑战点:你是否可以在不使用任何内置的SQL函数的情况下解决此问题。
+-----+------------+--------+ |Id | Company | Salary | +-----+------------+--------+ |5 | A | 451 | |6 | A | 513 | |12 | B | 234 | |9 | B | 1154 | |14 | C | 2645 | +-----+------------+--------+
select id,company,salary
from (select id,company,salary,row_number() over(partition by company order by salary) rn,count(*) over(partition by company) co
from employee
)
where rn in (ceil(co/2),co/2+1);
不用内置函数目前还没想到,会的朋友分享下,多谢!
未完待续。。。