Leetcode力扣 MySQL数据库 1549 每件商品对的最新订单

1549 每件商品对的最新订单

SQL架构

Create table If Not Exists Customers_1549 (customer_id int, name varchar(10));
Create table If Not Exists Orders_1549 (order_id int, order_date date, customer_id int, product_id int);
Create table If Not Exists Products_1549 (product_id int, product_name varchar(20), price int);
Truncate table Customers_1549;
insert into Customers_1549 (customer_id, name) values ('1', 'Winston');
insert into Customers_1549 (customer_id, name) values ('2', 'Jonathan');
insert into Customers_1549 (customer_id, name) values ('3', 'Annabelle');
insert into Customers_1549 (customer_id, name) values ('4', 'Marwan');
insert into Customers_1549 (customer_id, name) values ('5', 'Khaled');
Truncate table Orders_1549;
insert into Orders_1549 (order_id, order_date, customer_id, product_id) values ('1', '2020-07-31', '1', '1');
insert into Orders_1549 (order_id, order_date, customer_id, product_id) values ('2', '2020-7-30', '2', '2');
insert into Orders_1549 (order_id, order_date, customer_id, product_id) values ('3', '2020-08-29', '3', '3');
insert into Orders_1549 (order_id, order_date, customer_id, product_id) values ('4', '2020-07-29', '4', '1');
insert into Orders_1549 (order_id, order_date, customer_id, product_id) values ('5', '2020-06-10', '1', '2');
insert into Orders_1549 (order_id, order_date, customer_id, product_id) values ('6', '2020-08-01', '2', '1');
insert into Orders_1549 (order_id, order_date, customer_id, product_id) values ('7', '2020-08-01', '3', '1');
insert into Orders_1549 (order_id, order_date, customer_id, product_id) values ('8', '2020-08-03', '1', '2');
insert into Orders_1549 (order_id, order_date, customer_id, product_id) values ('9', '2020-08-07', '2', '3');
insert into Orders_1549 (order_id, order_date, customer_id, product_id) values ('10', '2020-07-15', '1', '2');
Truncate table Products_1549;
insert into Products_1549 (product_id, product_name, price) values ('1', 'keyboard', '120');
insert into Products_1549 (product_id, product_name, price) values ('2', 'mouse', '80');
insert into Products_1549 (product_id, product_name, price) values ('3', 'screen', '600');
insert into Products_1549 (product_id, product_name, price) values ('4', 'hard disk', '450');

表: 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 是该表主键.
该表包含所有商品的信息.
 

写一个SQL 语句, 找到每件商品的最新订单(可能有多个).

返回的结果以 product_name 升序排列, 如果有排序相同, 再以 product_id 升序排列. 如果还有排序相同, 再以 order_id 升序排列.

查询结果格式如下例所示:

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   |
+------------+--------------+-------+

Result
+--------------+------------+----------+------------+
| 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 没有被下单, 我们不把它包含在结果表中.

解题

select product_name, o.product_id, order_id, order_date
from Orders_1549 o left join Products_1549 p
using(product_id)
where (product_id, order_date) in
(
    select product_id, max(order_date) order_date
    from Orders_1549
    group by product_id
)
order by product_name, product_id, order_id;