当前位置：首页 > article >正文

SQL实战训练之，力扣：1532最近的三笔订单

article 2024/10/27 21:03:09

一、力扣原题链接

二、题目描述

三、建表语句

四、题目分析

五、SQL解答

六、最终答案

七、验证

八、知识点

一、力扣原题链接

1532. 最近的三笔订单

二、题目描述

客户表：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 笔订单的通用解决方案吗?

三、建表语句

drop table if exists Customers;
drop table if exists 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);
Truncate table Customers;
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');
Truncate table Orders;
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');

四、题目分析

-- 1、排名，按照用户分组，日期倒序排列排名

-- 2、关联客户表带出客户名称

-- 3、筛选最近3笔订单

图就省略了。。。

五、SQL解答

with t1 as (
    select
        order_id, order_date, customer_id, cost,
        -- 1、排名，按照用户分组，日期倒序排列排名
        dense_rank() over (partition by customer_id order by order_date desc) dr
    from orders
)
select
    name as customer_name,
    c.customer_id,
    order_id,
    order_date
from t1
-- 2、关联客户表带出客户名称
join customers c on c.customer_id = t1.customer_id
-- 3、筛选最近3笔订单
where dr <= 3
order by customer_name,customer_id,order_date desc
;

六、最终答案

with t1 as (
    select
        order_id, order_date, customer_id, cost,
        -- 1、排名，按照用户分组，日期倒序排列排名
        dense_rank() over (partition by customer_id order by order_date desc) dr
    from orders
)
select
    name as customer_name,
    c.customer_id,
    order_id,
    order_date
from t1
-- 2、关联客户表带出客户名称
join customers c on c.customer_id = t1.customer_id
-- 3、筛选最近3笔订单
where dr <= 3
order by customer_name,customer_id,order_date desc
;