MySQL 查询 Select、SUM、LEFT JOIN

Question

名为 stock 的表 1 包含列 Items
名为 Products_sold 的表 2 包含商品、数量
名为 products_purchased 的表 3 包含商品、数量

我需要获取三列的sql查询，例如

(产品)(表 1 中的所有项目)
(已售出的产品)(product_sold 表中每件商品的数量总和)
(购买的产品)(product_purchased 表中每件商品的数量总和)

我有查询，但它不能正常工作

  Select stock.Items as Products, 
         SUM(Products_sold.Quantity) as [Products Sold], 
         SUM(Products_purchased.Quantity) as [Products purchased]
  From 
         (
           (Stock LEFT JOIN products_purchased ON stock.Items=products_purchased.Items) 
           LEFT JOIN products_sold ON stock.Items=products_sold.Item
         )
  Group By stock.Items

Answer 1

试试这个:

SELECT S.Items as Products, 
       COALESCE(PS.Quantity, 0) as [Products Sold], 
       COALESCE(PP.Quantity, 0) as [Products purchased]
From Stock S 
LEFT JOIN (SELECT Items, SUM(Quantity) AS Quantity 
           FROM products_purchased 
           GROUP BY Items
         ) AS PP ON S.Items = PP.Items 
LEFT JOIN (SELECT Items, SUM(Quantity) AS Quantity 
           FROM products_sold 
           GROUP BY Items
         ) AS PS ON S.Items = PS.Items;