多表查询中的多个sun函数

sh7euo9m 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(331)

我在mysql数据库上有3个表。产品，入库，出库。我想按产品id计算每个产品的总库存和总库存。
我使用下面的查询。但它给了我一些错误的答案。

SELECT product.Product_Name, SUM(stock_in.StockInQuantity) as stockin, S
        SUM(stock_out.Quantity) as stockout 
FROM product, stock_in, stock_out 
WHERE stock_in.Product_ID=product.Product_ID 
and stock_out.Product_ID=product.Product_ID 
GROUP BY product.Product_ID**

但是如果我用单独的查询 stockin 以及 stockout ，结果正确。

SELECT Product_Name, SUM(StockInQuantity) as stockin 
FROM product, stock_in 
WHERE stock_in.Product_ID=product.Product_ID 
GROUP BY product.Product_ID

SELECT Product_Name, SUM(Quantity) as stockout 
FROM product, stock_out 
WHERE stock_out.Product_ID=product.Product_ID 
GROUP BY product.Product_ID**

所以请给我一个解决方案，在一个查询中找到结果，或者在一个表中回显两个查询结果。。

mysql php sum

来源：https://stackoverflow.com/questions/53829339/multiple-sun-function-in-a-query-in-multiple-tables

1条答案

按热度按时间

eaf3rand1#

您可以尝试以下查询：

SELECT p.product_name AS Product_Name,
   COALESCE(SUM(si.stockin), 0) AS total_stockin,
   COALESCE(SUM(so.stockout), 0) AS total_stockout,
FROM product p
LEFT JOIN (SELECT Product_ID, SUM(StockInQuantity) AS stockin
       FROM stock_in 
       GROUP BY Product_ID) si ON si.Product_ID = p.Product_ID
LEFT JOIN (SELECT Product_ID, SUM(Quantity) AS stockout
       FROM stock_out 
       GROUP BY Product_ID) so ON so.Product_ID = p.Product_ID
GROUP BY Product_Name

赞(0）回复(0）举报 2021-06-16

我来回答

多表查询中的多个sun函数

1条答案

相关问题

热门标签

最新问答