mysql - 4表MYSQL中的总和

Question

我正在尝试对 4 个表中的数量求和，我希望结果如图所示:例子:item1 初始数量 = 10 + item1 购买数量 =10所以上面两个表的项目之和是20。现在我想将另一个表中的相同 item1 求和..这是:15 结果应该是:20-15=5我试过总和:

select productName,
sum(initial.quantity)+sum(buy.quantity) as total1
-
sum(sale.quantity)+sum(free.quantity) as total2
from products
group by productName

Answer 1

如果您的表不一定具有所有表中所有项目的值(即具有初始值，但还没有销售)，这样做会更安全:

select p.productName, sum(q.iquantity)+sum(q.bquantity) - sum(q.squantity) - sum(q.fquantity) as 'quantity'
from products p
  join (
    select product_id, quantity as iquantity, 0 as bquantity,  0 as squantity, 0 as fquantity
    from initial 
    union
    select product_id, 0, quantity, 0, 0
    from buy
    union
    select product_id, 0, 0, quantity, 0
    from sale
    union
    select product_id, 0, 0, 0, quantity
    from free
  ) as q on q.product_id = p.product_id
group by p.productName