SQL查询：获取未完全发货订单的下一预计到货日期

核心优化逻辑 #

• 跳过无意义的全表扫描：不需要遍历所有已完全发货的订单项做无效计算，提前基于最小粒度聚合数据，减少关联计算量
• 剔除冗余表关联：Shipment主表在当前需求中没有需要取的字段，不需要参与连接，避免不必要的性能损耗
• 利用覆盖索引特性：通过配套索引设计，让查询全程不需要回表读取数据页，大幅降低IO开销

推荐配套性能优化索引 #

以下索引可覆盖查询全流程的字段读取需求，在大数据量场景下比无索引查询快1-2个数量级

• ShipmentItem表联合索引：(SalesOrderItemId, Qty)，覆盖订单项关联、累计已发量计算的全部字段
• SalesOrderItem表联合索引：(SalesOrderId, SalesOrderItemId, Qty, DateExpected)，覆盖订单维度聚合、数量对比、预计到货日期读取的全部字段

高效实现SQL #

SELECT
    soi.SalesOrderId,
    MIN(soi.DateExpected) AS NextDateExpected
FROM SalesOrderItem soi
LEFT JOIN (
    -- 提前按订单项粒度聚合已发货数量，避免大表关联时重复计算
    SELECT
        SalesOrderItemId,
        SUM(Qty) AS TotalShippedQty
    FROM ShipmentItem
    GROUP BY SalesOrderItemId
) shipped ON shipped.SalesOrderItemId = soi.SalesOrderItemId
WHERE
    -- 判定未完全发货：无发货记录则已发量记为0，只要小于订购量即标记为未结
    ISNULL(shipped.TotalShippedQty, 0) < soi.Qty
GROUP BY soi.SalesOrderId

方案对比说明 #

• 相比最初的实现思路，该方案没有先关联所有发货主表、也没有先拉取所有订单项做全量遍历，聚合过程全部在最小数据粒度完成，数据库优化器可自动选择流式聚合算法，避免大内存排序/哈希的开销
• 最终返回的结果完全符合字段要求：仅输出销售订单ID、对应下一预计到货日期（无有效预计时间时自动返回NULL）

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