题目描述：

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

写一个解决方案, 找到每件商品的最新订单(可能有多个).

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

示例：

js
输入:
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   |
+------------+--------------+-------+

输出：
+--------------+------------+----------+------------+
| 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 |
+--------------+------------+----------+------------+

思路解析：WITH subquery + rank()窗口函数

在子查询中先选定我们需要的结果列，并使用rank()函数按照order_date降序排列后赋予排名； 在主查询中限定rnk=1，即可得到需要的结果。

参考代码：

sql
# WITH subquery
with t1 as (
select p.product_name, o.product_id, o.order_id, o.order_date,
rank() over (partition by p.product_name order by o.order_date desc) rnk
from
Orders o left join Products p on o.product_id=p.product_id)

# main query
select t1.product_name, t1.product_id, t1.order_id, t1.order_date from t1
where t1.rnk = 1
order by t1.product_name asc, t1.product_id asc, t1.order_id asc

注意：

要使用rank()而不能使用row_number()，因为后者会赋予每一行一个唯一的行号，而前者会对于 order by 内相同的内容赋予相同的排名，但若存在重复，则会跳去下一个原本应有的值，例如：

sql
SELECT 
    product_id,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY order_date DESC) AS rn
FROM 
    orders;
    

product_id	order_date	rnk
1	2024-07-01	1
1	2024-07-01	2
1	2024-06-30	3
2	2024-07-02	1
2	2024-07-01	2

sql
SELECT 
    product_id,
    order_date,
    RANK() OVER (PARTITION BY product_id ORDER BY order_date DESC) AS rnk
FROM 
    orders;
    
product_id	order_date	rnk
1	2024-07-01	1
1	2024-07-01	1
1	2024-06-30	3
2	2024-07-02	1
2	2024-07-01	2