mysql - mysql中的求和查询，带有减法和连接

Question

所以我有两个表:supply_inventory_list 和 supply_employee_list

这是supply_inventory_list

supply_id  quantity  unit    item_name         supply_description      date_received         
---------  --------  ------  ----------------  ----------------------  -------------  
        1         5  REAM    LEGAL BOND PAPER  CANON LEGAL BOND PAPER  2019-08-28
        2         5  REAM    LEGAL BOND PAPER  HP LEGAL BOND PAPER     2019-08-30
        3         5  REAM    A4 BOND PAPER     CANON A4 BOND PAPER     2019-08-28
        4         5  REAM    A4 BOND PAPER     HP A4 BOND PAPER        2019-08-30
        5         3  PIECE   PENCIL            FABER-CASTELL GRIP 2011 2019-08-26

这是supply_employee_list

emp_supply_id  supply_id  deployed_quantity  employee_name  date_deployed       
-------------  ---------  -----------------  -------------  -------------------  
            1          2                  2  ALEX WILLARD   2019-08-28 08:16:00 
            2          1                  3  MARK JACOBS    2019-08-28 08:18:00 
            3          3                  1  DAVE SALES     2019-08-28 08:18:00 
            4          4                  1  JOE COLLINS    2019-08-28 08:31:00

现在我想按 item_name 对 supply_inventory_list 组中的 quantity 求和，并减去 deployed_quantity supply_employee_list。所以总数量应该是supply_inventory_list中的数量之和减去supply_employee_list中的deducted_quantity。我尝试了这个查询，但结果是错误的。

SELECT 
  SIL.supply_id,
  SUM(SIL.quantity) - IFNULL(SEL.deployed_quantity, 0) AS 'AVAILABLE QUANTITY',
  SIL.unit,
  SIL.item_name ,
  SIL.supply_description,
  IFNULL(SEL.deployed_quantity, 0) AS 'deployed_quantity' 
FROM
  supply_inventory_list SIL 
  LEFT JOIN 
    (SELECT 
      supply_id,
      SUM(deployed_quantity) deployed_quantity 
    FROM
      supply_employee_list 
    GROUP BY supply_id) SEL 
    ON SEL.`supply_id` = SIL.`supply_id` 
GROUP BY SIL.item_name 
ORDER BY SIL.item_name ASC 

这是结果，但它是错误的:

supply_id  AVAILABLE QUANTITY  unit    item_name         supply_description      deployed_quantity  
---------  ------------------  ------  ----------------  ----------------------  -------------------
        3  9                   REAM    A4 BOND PAPER     CANON A4 BOND PAPER     1                  
        1  7                   REAM    LEGAL BOND PAPER  CANON LEGAL BOND PAPER  3  
        5  3                   PIECE   PENCIL            FABER-CASTELL GRIP 2011 0

结果应该是这样的:

supply_id  AVAILABLE QUANTITY  unit    item_name         supply_description      deployed_quantity  
---------  ------------------  ------  ----------------  ----------------------  -------------------
        3  8                   REAM    A4 BOND PAPER     CANON A4 BOND PAPER     2                  
        1  5                   REAM    LEGAL BOND PAPER  CANON LEGAL BOND PAPER  5         
        5  3                   PIECE   PENCIL            FABER-CASTELL GRIP 2011 0                  

A4 BOND PAPER 的可用数量为 8，因为总数量为 10，并且有两名员工各部署 1 REAM A4 BOND PAPER。对于 LEGAL BOND PAPER 为 5，因为一名员工部署了 2，另一名员工部署了 3。如何实现这一目标？

Answer 1

请检查这是否符合您的要求。

SELECT min(supply_id), sum(A.aq), min(A.unit), A.item_name, MIN(A.supply_description) as supply_description
, MAX(A.deployed_quantity) as deployed_quantity from
(SELECT 
  SIL.quantity - COALESCE(SEL.deployed_quantity, 0) AS aq,
  SIL.unit,
  SIL.item_name ,
  SIL.supply_description,
  SIL.supply_id,
  SEL.deployed_quantity
FROM
  supply_inventory_list SIL 
  LEFT JOIN 
    (SELECT 
      supply_id,
      SUM(deployed_quantity) deployed_quantity 
    FROM
      supply_employee_list 
    GROUP BY supply_id) SEL 
    ON SEL.supply_id = SIL.supply_id) as A
GROUP BY A.item_name
ORDER BY A.item_name ASC ;