mysql - SQL 查询结果有重复值

Question

我有三个表，1.ct_product2.入库3. 缺货

表结构和数据可用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

Answer 1

您需要在整体查询上添加一个分组依据，并将 SUM 添加到项目周围以达到总计:

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