近几天写了一下某硅谷的hivesql题去了，力扣停了一下一天写了一题，今天更两篇。
1. 力扣1789：员工的直属部门
1.1 题目：
表：Employee
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| employee_id | int |
| department_id | int |
| primary_flag | varchar |
+---------------+---------+
这张表的主键为 employee_id, department_id (具有唯一值的列的组合)
employee_id 是员工的ID
department_id 是部门的ID，表示员工与该部门有关系
primary_flag 是一个枚举类型，值分别为('Y', 'N'). 如果值为'Y',表示该部门是员工的直属部门。 如果值是'N',则否
一个员工可以属于多个部门。当一个员工加入超过一个部门的时候，他需要决定哪个部门是他的直属部门。请注意，当员工只加入一个部门的时候，那这个部门将默认为他的直属部门，虽然表记录的值为'N'.
请编写解决方案，查出员工所属的直属部门。
返回结果 没有顺序要求 。
返回结果格式如下例子所示：
示例 1：
输入： Employee table: +-------------+---------------+--------------+ | employee_id | department_id | primary_flag | +-------------+---------------+--------------+ | 1 | 1 | N | | 2 | 1 | Y | | 2 | 2 | N | | 3 | 3 | N | | 4 | 2 | N | | 4 | 3 | Y | | 4 | 4 | N | +-------------+---------------+--------------+ 输出： +-------------+---------------+ | employee_id | department_id | +-------------+---------------+ | 1 | 1 | | 2 | 1 | | 3 | 3 | | 4 | 3 | +-------------+---------------+ 解释： - 员工 1 的直属部门是 1 - 员工 2 的直属部门是 1 - 员工 3 的直属部门是 3 - 员工 4 的直属部门是 3
1.2 思路：
分为两种情况，然后union all结果即可。
1.3 题解：
-- 先找出员工只进入一个部门
with tep1 as (
select employee_id
from Employee
group by employee_id
having count(*) <= 1
), tep2 as (
select employee_id, department_id
from Employee
where employee_id in (select * from tep1)
), tep3 as (
-- 然后查找在员工进多个部门的情况然后选出primary_flag为‘Y’的记录
select employee_id, department_id
from Employee
where employee_id not in (select * from tep1)
and primary_flag = 'Y'
)
select *
from tep2
union all
select *
from tep32. 力扣1549：每件商品的最新订单
2.1 题目：
表: Customers
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | +---------------+---------+ customer_id 是该表主键. 该表包含消费者的信息.
表: Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | customer_id | int | | product_id | int | +---------------+---------+ order_id 是该表主键. 该表包含消费者customer_id产生的订单. 不会有商品被相同的用户在一天内下单超过一次.
表: Products
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | product_name | varchar | | price | int | +---------------+---------+ product_id 是该表主键. 该表包含所有商品的信息.
写一个解决方案, 找到每件商品的最新订单(可能有多个).
返回的结果以 product_name 升序排列, 如果有排序相同, 再以 product_id 升序排列. 如果还有排序相同, 再以 order_id 升序排列.
查询结果格式如下例所示。
示例 1：
输入:Customers表：+-------------+-----------+ | customer_id | name | +-------------+-----------+ | 1 | Winston | | 2 | Jonathan | | 3 | Annabelle | | 4 | Marwan | | 5 | Khaled | +-------------+-----------+Orders表：+----------+------------+-------------+------------+ | order_id | order_date | customer_id | product_id | +----------+------------+-------------+------------+ | 1 | 2020-07-31 | 1 | 1 | | 2 | 2020-07-30 | 2 | 2 | | 3 | 2020-08-29 | 3 | 3 | | 4 | 2020-07-29 | 4 | 1 | | 5 | 2020-06-10 | 1 | 2 | | 6 | 2020-08-01 | 2 | 1 | | 7 | 2020-08-01 | 3 | 1 | | 8 | 2020-08-03 | 1 | 2 | | 9 | 2020-08-07 | 2 | 3 | | 10 | 2020-07-15 | 1 | 2 | +----------+------------+-------------+------------+Products表：+------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 1 | keyboard | 120 | | 2 | mouse | 80 | | 3 | screen | 600 | | 4 | hard disk | 450 | +------------+--------------+-------+ 输出： +--------------+------------+----------+------------+ | product_name | product_id | order_id | order_date | +--------------+------------+----------+------------+ | keyboard | 1 | 6 | 2020-08-01 | | keyboard | 1 | 7 | 2020-08-01 | | mouse | 2 | 8 | 2020-08-03 | | screen | 3 | 3 | 2020-08-29 | +--------------+------------+----------+------------+ 解释： keyboard 的最新订单在2020-08-01, 在这天有两次下单. mouse 的最新订单在2020-08-03, 在这天只有一次下单. screen 的最新订单在2020-08-29, 在这天只有一次下单. hard disk 没有被下单, 我们不把它包含在结果表中.
2.2 思路：
最新=>排名第一=>窗口函数=>dense_rank
2.3 题解：
-- 看了一下输出表，感觉Customers用不到
-- 最新订单，一眼窗口函数
-- 以product_id分区，order_date降序，ranks为1即是最新的订单
-- 使用dense_rank是因为每个玩意的最新订单不止一个
with tep as (
select order_id, order_date , o.product_id product_id , product_name,
dense_rank() over (partition by product_id order by order_date desc) ranks
from Orders o
join Products p
on o.product_id = p.product_id
)
-- 只查找出ranks为1的记录，并order by排序
select product_name, product_id, order_id, order_date
from tep
where ranks = 1
order by product_name, product_id, order_id 3. 力扣1532：最近的三笔订单
3.1 题目：
表：Customers
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | +---------------+---------+ customer_id 是该表具有唯一值的列 该表包含消费者的信息
表：Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | customer_id | int | | cost | int | +---------------+---------+ order_id 是该表具有唯一值的列 该表包含 id 为 customer_id 的消费者的订单信息 每一个消费者 每天一笔订单
写一个解决方案，找到每个用户的最近三笔订单。如果用户的订单少于 3 笔，则返回他的全部订单。
返回的结果按照 customer_name 升序 排列。如果有相同的排名，则按照 customer_id 升序 排列。如果排名还有相同，则按照 order_date 降序 排列。
结果格式如下例所示：
示例 1:
输入：Customers+-------------+-----------+ | customer_id | name | +-------------+-----------+ | 1 | Winston | | 2 | Jonathan | | 3 | Annabelle | | 4 | Marwan | | 5 | Khaled | +-------------+-----------+Orders+----------+------------+-------------+------+ | order_id | order_date | customer_id | cost | +----------+------------+-------------+------+ | 1 | 2020-07-31 | 1 | 30 | | 2 | 2020-07-30 | 2 | 40 | | 3 | 2020-07-31 | 3 | 70 | | 4 | 2020-07-29 | 4 | 100 | | 5 | 2020-06-10 | 1 | 1010 | | 6 | 2020-08-01 | 2 | 102 | | 7 | 2020-08-01 | 3 | 111 | | 8 | 2020-08-03 | 1 | 99 | | 9 | 2020-08-07 | 2 | 32 | | 10 | 2020-07-15 | 1 | 2 | +----------+------------+-------------+------+ 输出： +---------------+-------------+----------+------------+ | customer_name | customer_id | order_id | order_date | +---------------+-------------+----------+------------+ | Annabelle | 3 | 7 | 2020-08-01 | | Annabelle | 3 | 3 | 2020-07-31 | | Jonathan | 2 | 9 | 2020-08-07 | | Jonathan | 2 | 6 | 2020-08-01 | | Jonathan | 2 | 2 | 2020-07-30 | | Marwan | 4 | 4 | 2020-07-29 | | Winston | 1 | 8 | 2020-08-03 | | Winston | 1 | 1 | 2020-07-31 | | Winston | 1 | 10 | 2020-07-15 | +---------------+-------------+----------+------------+ 解释： Winston 有 4 笔订单, 排除了 "2020-06-10" 的订单, 因为它是最老的订单。 Annabelle 只有 2 笔订单, 全部返回。 Jonathan 恰好有 3 笔订单。 Marwan 只有 1 笔订单。 结果表我们按照 customer_name 升序排列，customer_id 升序排列，order_date 降序排列。
进阶：
- 你能写出最近
n笔订单的通用解决方案吗?
3.2 思路：
最近三比=>排名前三=>窗口函数=>rank()
3.3 题解：
with tep as (
-- 先join将两张表连接，并select给出要输出的哪些字段
-- 接下来使用窗口函数给窗口的记录增加排名字段
-- partition by指定的分区，order by指定的排序规则
-- ranks小于等于3的即使最近的三比订单
select name customer_name, c1.customer_id customer_id, order_id,order_date,
rank() over (partition by customer_id order by name, c1.customer_id, order_date desc) ranks
from Customers c1
join Orders o1
on o1.customer_id = c1.customer_id
)
-- 将ranks大于3的记录过滤即可
select customer_name, customer_id, order_id, order_date
from tep
where ranks <= 3
order by customer_name, customer_id, order_date desc
扫一扫
889
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
