如何编写SQL查询获取最新状态为已交付的订单总商品数量？

需要编写SQL查询，统计最新状态为delivered的订单的总商品数量。

数据表结构 #

table_1（订单商品表） #

table_2（订单状态表） #

原查询的问题 #

以下查询未考虑订单的最新状态，导致结果错误：

SELECT SUM(t1.quantity) AS total  
FROM table_1 t1  
LEFT JOIN table_2 t2    
ON t1.order_id = t2.order_id 
WHERE t2.order_status = 'delivered'

该查询返回结果为9（3+1+5），但订单100001的最新状态是returned，不应将其3件商品计入总数，正确结果应为6（1+5）。

正确的SQL查询 #

方法：使用窗口函数筛选最新状态 #

SELECT SUM(t1.quantity) AS total
FROM table_1 t1
INNER JOIN (
    -- 筛选出每个订单的最新状态且状态为delivered的记录
    SELECT order_id, order_status
    FROM (
        -- 给每个订单的状态按时间倒序编号，最新状态编号为1
        SELECT 
            order_id, 
            order_status,
            ROW_NUMBER() OVER (
                PARTITION BY order_id 
                ORDER BY order_date DESC, id DESC
            ) AS rn
        FROM table_2
    ) latest_status
    WHERE rn = 1 AND order_status = 'delivered'
) valid_orders ON t1.order_id = valid_orders.order_id

逻辑说明 #

1. 最内层子查询：用ROW_NUMBER()窗口函数，按order_id分组，对每个订单的状态记录按order_date倒序（日期相同则按id倒序）编号，最新的状态会被标记为rn=1。
2. 中间子查询：筛选出编号为1的记录（即每个订单的最新状态），并且只保留状态为delivered的订单。
3. 外层查询：关联订单商品表，对符合条件的订单商品数量求和，得到正确结果。

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