MySQL-如何通过连接三个表和过滤器来获得特定列的和

Question

有三张桌子

1. 库存-包含库存信息
2. 订单-包含订单的存储信息
3. OrderParts-包含与订单关联的部分

库存表

订单表

订购零件表

需要生成以下预期报告

查询试图实现上述结果

SELECT 
    inventory.partNumber, inventory.storeId, SUM(parts.quantity) as orderedQuantity, 
    inventory.availableQuantity
    FROM
        (SELECT *, SUM(availableQuantity) AS availQty
FROM
    inventory AS inventory
GROUP BY storeId , partNumber) AS inventory
    LEFT JOIN
(SELECT 
    *
FROM
    order
GROUP BY storeId) AS order ON order.storeId = inventory.storeId
    LEFT JOIN
(SELECT 
    *
FROM
    orderParts) AS parts ON inventory.partNumber = parts.partNumbe    
where inventory.partNumber in ("A1234", "B1234");
GROUP BY order.storeId , parts.partNumber

获取已订购数量为空

请告诉我如何得到预期的累计订货量。提前道谢。

Answer 1

一种比较简单的方法是联合所有和聚合：

select storeid, partnumber,
       sum(availableqty) as availableqty,
       sum(orderedqty) as orderedqty
from (select storeid, partnumber, availableqty, 0 as orderedqty
      from inventory
      union all
      select o.storeid, op.partnumber, 0, op.quantity
      from orderparts op join
           orders o
           on op.ordernumber = o.ordernumber
     ) sp
group by storeid, partnumber;

您的问题没有明确描述筛选器，但您可以在子查询或外部查询中添加筛选器。