MySQL多表关联求和：仅统计OrderItemList中存在的订单数据

表结构与数据 #

OrderItemList表 #

| OrderNo | ItemNo | Loc | Qty |
|---------|--------|-----|-----|
| 100     | A      | 1   | 1   |
| 101     | A      | 1   | 2   |
| 102     | A      | 1   | 1   |
| 103     | A      | 2   | 1   |
| 104     | A      | 2   | 1   |

OrderHistoryLog表 #

| OrderNo | ItemNo | Loc | Qty |
|---------|--------|-----|-----|
| 50      | A      | 1   | 5   |
| 51      | A      | 1   | 2   |
| 100     | A      | 1   | 1   |
| 102     | A      | 1   | 3   |
| 103     | A      | 2   | 1   |

需求说明 #

查询OrderItemList全量记录，同时新增两个字段：

• HistQty：对应订单在OrderHistoryLog中的Qty值，无匹配则为0
• LocHistQty：仅统计OrderItemList中存在的订单，按ItemNo和Loc汇总OrderHistoryLog的Qty总和

预期结果 #

| OrderNo | ItemNo | Loc | Qty | HistQty | LocHistQty |
|---------|--------|-----|-----|---------|------------|
| 100     | A      | 1   | 1   | 1       | 4          |
| 101     | A      | 1   | 2   | 0       | 4          |
| 102     | A      | 1   | 1   | 3       | 4          |
| 103     | A      | 2   | 1   | 1       | 1          |
| 104     | A      | 2   | 1   | 0       | 1          |

现有SQL问题 #

当前编写的SQL中，LocHistQty统计了OrderHistoryLog中所有订单的Qty，而非仅OrderItemList中存在的订单，无法满足需求：

select OI.OrderNo, OI.ItemNo, OI.Loc, OI.Qty, IFNULL(OL.Qty, 0) as HistQty, OL2.LocHistQty
from OrderItemList OI 
left join OrderItemLog OL on OL.OrderNo = OI.OrderNo and OL.ItemNo = OI.ItemNo
join 
(
    select ItemNo, Loc, sum(qty) as LocHistQty
    from zOrderItemLog 
    group by ItemNo, Loc
) as OL2 
on OL2.ItemNo = OI.ItemNo and OL2.Loc = OI.Loc 
order by OrderNo

解决方案 #

修正后的SQL #

通过在汇总子查询中先关联OrderItemList过滤订单范围，确保只统计目标订单的历史记录：

SELECT 
    OI.OrderNo,
    OI.ItemNo,
    OI.Loc,
    OI.Qty,
    IFNULL(OL.Qty, 0) AS HistQty,
    OL2.LocHistQty
FROM OrderItemList OI
LEFT JOIN OrderHistoryLog OL 
    ON OL.OrderNo = OI.OrderNo 
    AND OL.ItemNo = OI.ItemNo
JOIN (
    -- 仅保留OrderItemList中存在的订单的历史记录，再按Item+Loc汇总
    SELECT 
        OH.ItemNo,
        OH.Loc,
        SUM(OH.Qty) AS LocHistQty
    FROM OrderHistoryLog OH
    INNER JOIN OrderItemList OI_filter 
        ON OH.OrderNo = OI_filter.OrderNo
        AND OH.ItemNo = OI_filter.ItemNo
    GROUP BY OH.ItemNo, OH.Loc
) OL2 
    ON OL2.ItemNo = OI.ItemNo 
    AND OL2.Loc = OI.Loc
ORDER BY OI.OrderNo;

性能优化建议 #

针对大数据量场景，可通过以下方式提升查询效率：

• 给OrderHistoryLog建立(OrderNo, ItemNo)联合索引，加速关联过滤
• 给OrderItemList建立(OrderNo, ItemNo)联合索引，提升订单范围过滤速度
• 若数据库支持CTE（如MySQL 8+、PostgreSQL），可改用CTE提升可读性：

WITH FilteredOrders AS (
    SELECT DISTINCT OrderNo, ItemNo FROM OrderItemList
)
SELECT 
    OI.OrderNo,
    OI.ItemNo,
    OI.Loc,
    OI.Qty,
    IFNULL(OL.Qty, 0) AS HistQty,
    OL2.LocHistQty
FROM OrderItemList OI
LEFT JOIN OrderHistoryLog OL 
    ON OL.OrderNo = OI.OrderNo 
    AND OL.ItemNo = OI.ItemNo
JOIN (
    SELECT 
        OH.ItemNo,
        OH.Loc,
        SUM(OH.Qty) AS LocHistQty
    FROM OrderHistoryLog OH
    INNER JOIN FilteredOrders FO
        ON OH.OrderNo = FO.OrderNo
        AND OH.ItemNo = FO.ItemNo
    GROUP BY OH.ItemNo, OH.Loc
) OL2 
    ON OL2.ItemNo = OI.ItemNo 
    AND OL2.Loc = OI.Loc
ORDER BY OI.OrderNo;

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