PostgreSQL：查询每个客户最新订单的状态

数据库表结构与数据 #

Customer表 #

CustomerOrder表 #

OrderStatus表 #

查询需求 #

编写SQL语句，展示每个客户最新订单（订单id最大的记录）的状态，预期结果如下：

预期结果 #

实现方案 #

方法一：使用窗口函数（通用型强） #

SELECT 
    customer,
    status AS latest_order_status
FROM (
    SELECT 
        customer,
        status,
        ROW_NUMBER() OVER (PARTITION BY customer ORDER BY id DESC) AS row_num
    FROM CustomerOrder
) AS order_ranked
WHERE row_num = 1;

方法二：使用关联子查询 #

SELECT 
    co.customer,
    co.status AS latest_order_status
FROM CustomerOrder co
WHERE co.id = (
    SELECT MAX(id) 
    FROM CustomerOrder 
    WHERE customer = co.customer
);

内容的提问来源于stack exchange，提问作者ranalto