所以我试图让下面的代码适用于MySQL:
SELECT ProdDate
,PastProdDate
,sum(PastQuantity) + sum(ProdQuantity) AS 'Total Quantity'
,sum(ProdPrice * ProdQuantity) + sum(PastPrice * PastQuantity) AS 'Total'
FROM supply
LEFT JOIN PastSupply ON Supply.ProdID = PastSupply.ProdIDPast
GROUP BY ProdDate
,PastProdDate;
代码的问题似乎是,如果 PastSupply 表中没有任何内容,则"总量"和"总计"也为空。我知道为什么会发生这种情况,但我想知道如何解决这个问题。我尝试添加默认值,但这似乎也不起作用。
使用 COALESCE
:
Select ProdDate,
PastProdDate,
coalesce(sum(PastQuantity),0) +
coalesce(sum(ProdQuantity),0) as 'Total Quantity',
coalesce(sum(ProdPrice * ProdQuantity),0) +
coalesce(sum(PastPrice * PastQuantity),0) as 'Total'
from supply
left join PastSupply
on Supply.ProdID = PastSupply.ProdIDPast
group by ProdDate,PastProdDate;