sql - 减去两个 SUM GROUP BY 字段

Question

我有两张 table itemOrders和itemUsage .

itemOrders有两个字段:item_number和qty_ordered itemUsage有两个字段:item_number和qty_used

我正在尝试编写我的 SQL 查询，以便它总结两个表中每个项目编号的数量，然后减去 itemUsage 中的总数。来自itemOrders

到目前为止我已经想出了这个:

SELECT itemOrders.item_number
    ,(
        SELECT sum(qty_ordered)
        FROM itemOrders
        GROUP BY itemOrders.item_number
        ) - (
        SELECT sum(qty_used)
        FROM itemUsage
        GROUP BY itemUsage.item_number
        ) AS item_total
FROM itemOrders
INNER JOIN itemUsage 
        ON itemOrders.item_number = itemUsage.item_number
GROUP BY itemOrders.item_number

这里发生的情况是所有字段都为 0。

示例，如果商品编号“A”显示 itemOrders 中“A”的所有实例中订购的总数量为 3表中，itemUsage 中“A”的所有实例中仅使用了 1 个总数量。 table 。 SQL 应在 item_number 字段中的 1 个“A”实例旁边显示 item_total 字段中的数字 1。

Answer 1

问题是您正在创建一个CARTESIAN PRODUCT并重复SUM上的值，只需计算每个单独的值，然后LEFT JOIN两者。如果没有使用任何项目，COALESCE 会将 NULL 转换为 0

SELECT io_total.item_number,  
       order_total - COALESCE(used_total, 0) as item_total
FROM (SELECT io.item_number,  sum(io.qty_ordered) as order_total
      FROM itemOrders io
      GROUP BY io.item_number
     ) io_total
LEFT JOIN (SELECT iu.item_number, sum(iu.qty_used) as used_total 
           FROM itemUsage iu
           GROUP BY iu.item_number
          ) as iutotal
        ON io_total.item_number = iutotal.item_number