本文主要是介绍单挑力扣（LeetCode）SQL题：1532. 最近的三笔订单（难度：中等），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
题目：1532. 最近的三笔订单
（通过次数5,860 | 提交次数9,333，通过率62.79%）
表：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的消费者的订单信息
每一个消费者 每天一笔订单写一个 SQL 语句，找到每个用户的最近三笔订单。如果用户的订单少于 3 笔，则返回他的全部订单。
返回的结果按照 customer_name升序排列。如果排名有相同，则继续按照 customer_id 升序排列。如果排名还有相同，则继续按照 order_date 降序排列。查询结果格式如下例所示：
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 |
+----------+------------+-------------+------+Result table：
+---------------+-------------+----------+------------+
| 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笔订单的通用解决方案吗来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/the-most-recent-three-orders#测试数据
Create table If Not Exists Customers (customer_id int, name varchar(10));
Create table If Not Exists Orders (order_id int, order_date date, customer_id int, cost int);insert into Customers (customer_id, name) values ('1', 'Winston');
insert into Customers (customer_id, name) values ('2', 'Jonathan');
insert into Customers (customer_id, name) values ('3', 'Annabelle');
insert into Customers (customer_id, name) values ('4', 'Marwan');
insert into Customers (customer_id, name) values ('5', 'Khaled');insert into Orders (order_id, order_date, customer_id, cost) values ('1', '2020-07-31', '1', '30');
insert into Orders (order_id, order_date, customer_id, cost) values ('2', '2020-7-30', '2', '40');
insert into Orders (order_id, order_date, customer_id, cost) values ('3', '2020-07-31', '3', '70');
insert into Orders (order_id, order_date, customer_id, cost) values ('4', '2020-07-29', '4', '100');
insert into Orders (order_id, order_date, customer_id, cost) values ('5', '2020-06-10', '1', '1010');
insert into Orders (order_id, order_date, customer_id, cost) values ('6', '2020-08-01', '2', '102');
insert into Orders (order_id, order_date, customer_id, cost) values ('7', '2020-08-01', '3', '111');
insert into Orders (order_id, order_date, customer_id, cost) values ('8', '2020-08-03', '1', '99');
insert into Orders (order_id, order_date, customer_id, cost) values ('9', '2020-08-07', '2', '32');
insert into Orders (order_id, order_date, customer_id, cost) values ('10', '2020-07-15', '1', '2');解题思路：
虽然被标记为一道中等题目，但实际上，这只能算一道窗口函数的基本应用题。
题目要求，取出每个客户按时间排序最近的前3笔订单。
那么，很明显，我们需要根据客户来开窗；然后计算出每个客户每一笔交易的序号。
接着，根据序号，取出每个客户的前3笔订单。
最后，再跟客户信息表关联，取出客户姓名即可。
当然，题目要求，查询出的结果遵守一定的排序规则。这并不难，按照要求做一次排序，然后返回，就完成了。
参考SQL：
select c.name customer_name,b.customer_id,b.order_id,b.order_date
from
(selecta.customer_id,a.order_id,a.order_date,row_number() over(partition by a.customer_id order by a.order_date desc) rnfrom Orders a
)b
inner join Customers c
on b.customer_id = c.customer_id
where b.rn <= 3
order by c.name,b.customer_id,b.order_date desc;这篇关于单挑力扣（LeetCode）SQL题：1532. 最近的三笔订单（难度：中等）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
