面试题：MySQL多列排序中的复杂场景处理

Question

现有`orders`表，字段有`order_id`（订单ID）、`customer_id`（客户ID）、`order_date`（订单日期）、`total_amount`（订单总金额）。要求查询每个客户最近的3笔订单，按照客户ID升序，同一客户内按订单日期降序排列。请写出实现该需求的SQL语句。

Answer 1

WITH RankedOrders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        total_amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM
        orders
)
SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM
    RankedOrders
WHERE
    rn <= 3
ORDER BY
    customer_id ASC, order_date DESC;