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

要求：写一个 SQL 语句，找到每个用户的最近三笔订单。如果用户的订单少于 3 笔，则返回他的全部订单。

返回的结果按照 customer_name 升序排列。如果排名有相同，则继续按照 customer_id 升序排列，如果排名还有相同，则继续按照 order_date 降序排列。

表：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的消费者的订单信息每一个消费者 每天一笔订单

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 降序排列。

SQL语句：

with c as(select b.name as n1,a.customer_id as ci1,a.order_id as oi1,a.order_date as d1,row_number() over(partition by a.customer_id order by order_date desc) as r1from orders a join customers b on a.customer_id=b.customer_id) select n1 as customer_name,ci1 as customer_id,oi1 as order_id,d1 as order_datefrom c where r1<=3order by n1 asc,ci1 asc,d1 desc;

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