mysql - SQL查询结果具有重复值

我有三张桌子，
一。ct U产品
2。存货
三。缺货
表格结构和数据可用here
我想要的结果是
但是我的查询输出是
请告诉我怎样才能得到我想要的结果？
下面是SQL查询

SELECT
 (select product_name from ct_product where id=stock_in.ct_prod_id) as name,
 stockin.ct_prod_status,
 stockin.ct_prod_catgry,
 IFNull(stockin.stock_in, 0) stock_in,
 IFNull(stockout.stock_out, 0) stock_out,
 IFNull(stockin.stock_in, 0)-IFNull(stockout.stock_out, 0) stockinhand
FROM stock_in
LEFT JOIN
(
 SELECT
   SUM(quantity) stock_in,
  ct_prod_id,
   ct_prod_catgry,
   ct_prod_status
  FROM stock_in
  group by ct_prod_catgry, ct_prod_id, ct_prod_status
  ) stockin ON stockin.ct_prod_id = stock_in.ct_prod_id
  LEFT JOIN
 (
  SELECT
  SUM(quantity) stock_out,
   ct_prod_id,
    ct_prod_catgry,
    ct_prod_status
     FROM stock_out
     group by ct_prod_catgry, ct_prod_id, ct_prod_status
   ) stockout ON stockout.ct_prod_id = stock_in.ct_prod_id
  where stockout.ct_prod_catgry=stock_in.ct_prod_catgry and
   stockout.ct_prod_status=stock_in.ct_prod_status

最佳答案

您需要在整个查询中添加一个group by，并将SUM around添加到total项中：

SELECT
 (select product_name from ct_product where id=stock_in.ct_prod_id) as name,
 stockin.ct_prod_status,
 stockin.ct_prod_catgry,
 SUM(IFNull(stockin.stock_in, 0)) stock_in,
 SUM(IFNull(stockout.stock_out, 0)) stock_out,
 SUM(IFNull(stockin.stock_in, 0)-IFNull(stockout.stock_out, 0)) stockinhand
FROM stock_in
LEFT JOIN
(
 SELECT
   SUM(quantity) stock_in,
  ct_prod_id,
   ct_prod_catgry,
   ct_prod_status
  FROM stock_in
  group by ct_prod_catgry, ct_prod_id, ct_prod_status
  ) stockin ON stockin.ct_prod_id = stock_in.ct_prod_id
  LEFT JOIN
 (
  SELECT
  SUM(quantity) stock_out,
   ct_prod_id,
    ct_prod_catgry,
    ct_prod_status
     FROM stock_out
     group by ct_prod_catgry, ct_prod_id, ct_prod_status
   ) stockout ON stockout.ct_prod_id = stock_in.ct_prod_id
  where stockout.ct_prod_catgry=stock_in.ct_prod_catgry and
   stockout.ct_prod_status=stock_in.ct_prod_status
group by name, stockin.ct_prod_status, stockin.ct_prod_catgry

关于mysql - SQL查询结果具有重复值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45744314/