MySQL查询选择SUM，LEFT JOIN [英] MySQL query Select, SUM, LEFT JOIN

问题描述

Product_sold 包含商品，数量 商品包含商品列 >

Table3命名为 products_purchased ，其中包含项目，数量

（产品）（表1中的所有项目）三个栏目，如$ / b>

（产品） ）

（购买的产品）（product_purchased表中每件商品的总和）

我有查询但无法正常工作

 选择stock.Items as Products，
 SUM（Products_sold.Quantity）as [Products Sold]，
 SUM （购买产品）
从
（
（库存左加入products_purchased ON stock.Items = products_purchased.Items）
 LEFT JOIN products_sold ON stock.It ems = products_sold.Item 
）
 Group by stock.Items 
  

解决方案

试试这个：

  SELECT S.Items as Products，
 COALESCE（PS。数量，0）作为[产品销售]，
 COALESCE（PP.Quantity，0）as [产品购买] 
从库存S 
 LEFT JOIN（SELECT Items，SUM（Quantity）AS Quantity 
 FROM products_purchased 
 GROUP BY项目
）AS PP ON S.Items = PP.Items 
 LEFT JOIN（SELECT Items，SUM（Quantity）AS Quantity 
 FROM products_sold 
 GROUP BY项目
）AS PS ON S.Items = PS.Items; 
  

Table1 named stock contains column Items
Table2 named Products_sold contains Items, Quantity
Table3 named products_purchased contains Items, Quantity

I need sql query that gets three columns such as

(Products) (all items from table 1)
(Products sold) (sum of quantity of each items from product_sold table)
(Products purchased) (sum of quantity of each items from product_purchased table)

I have query but it does not work correctly

  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

解决方案

Try this:

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; 

这篇关于MySQL查询选择SUM，LEFT JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！