写一个 SQL 语句,找到每个用户的最近三笔订单。如果用户的订单少于 3 笔,则返回他的全部订单。
返回的结果按照 customer_name 升序排列。如果排名有相同,则继续按照 customer_id 升序排列。如果排名还有相同,则继续按照 order_date 降序排列。
SELECT
c.name customer_name,
c.customer_id,
t.order_id,
t.order_date
FROM (
SELECT
order_id,
order_date,
customer_id,
row_number() over(partition by customer_id order by order_date DESC) as rank_tag
FROM orders ) t
LEFT JOIN customers c
ON t.customer_id = c.customer_id
WHERE
rank_tag <= 3
ORDER BY
name, customer_id, order_date DESC;
作者:int_64
链接:https://leetcode.cn/problems/the-most-recent-three-orders/solution/by-jam007-u8na/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。