题解 | 每个顾客购买的最新产品名称
每个顾客购买的最新产品名称
https://www.nowcoder.com/practice/6ff37adae90f490aafa313033a2dcff7
# 子查询
select
o.customer_id,
customer_name,
product_name as latest_order
from
orders o
join customers c on o.customer_id = c.customer_id
join products p on o.product_id = p.product_id
where
(o.customer_id, order_date) in (
select
customer_id,
max(order_date)
from orders
group by customer_id
)
order by o.customer_id asc
# 窗口函数
select
customer_id,
customer_name,
latest_order
from (
select
o.customer_id,
customer_name,
product_name as latest_order,
row_number() over(partition by o.customer_id order by order_date desc) as rk
from
orders o
join customers c on o.customer_id = c.customer_id
join products p on o.product_id = p.product_id
group by o.customer_id, customer_name, product_name, order_date
)as a1
where rk = 1
order by customer_id asc
阿里巴巴开奖97人在聊