有三张桌子
库存表
订单表
订购零件表
需要生成以下预期报告
查询试图实现上述结果
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
获取已订购数量为空
请告诉我如何得到预期的累计订货量。提前道谢。
一种比较简单的方法是联合所有和聚合：
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;
您的问题没有明确描述筛选器，但您可以在子查询或外部查询中添加筛选器。
