题解 | 查询订单

WITH last_order AS (
    SELECT 
        *,
        rank() over (partition by customer_id order by order_date DESC) AS rk
    FROM orders
),
last_order_1 AS(
    SELECT 
        order_id,
        customer_id,
        order_date
    FROM last_order
    WHERE rk=1
)
SELECT 
    a.order_id,
    b.customer_name,
    a.order_date
FROM last_order_1 AS a
LEFT JOIN customers AS b
ON a.customer_id=b.customer_id
ORDER BY b.customer_name;