本文共 3117 字，大约阅读时间需要 10 分钟。

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

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

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

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 Table：

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

SQL语句：

with c as(select b.product_name as n1,b.product_id as p_id1,a.order_id as o_id1,order_date as d1,dense_rank() over(partition by a.product_id order by a.order_date desc) as rfrom orders ajoin products b on a.product_id=b.product_id )select n1 as product_name,p_id1 as product_id,o_id1 as order_id,d1 as order_datefrom cwhere r=1order by product_name asc,product_id asc,order_id asc;

转载地址：http://ingwi.baihongyu.com/