时间戳

首页 > 解决方案 > 在 MySQL 中使用 GROUP BY 求和和减法

mysql - 在 MySQL 中使用 GROUP BY 求和和减法

问题描述

我想显示供应中的剩余数量。

我有两张桌子：

`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-27
        2         5  REAM    A4 BOND PAPER     PAPER ONE A4 BOND PAPER  2019-08-27
        3         5  REAM    LEGAL BOND PAPER  HP LEGAL BOND PAPER      2019-08-30

`supply_employee_list` 表：

emp_supply_id  supply_id  deployed_quantity  employee_name            date_deployed        
-------------  ---------  -----------------  -----------------------  --------------------
            1          3                  2  ALEX WARD                2019-08-29 16:00:00  
            2          3                  1  EDWARD COLLINS           2019-08-29 16:00:00

这是我尝试过的查询，但没有得到我想要的结果：

SELECT 
    supply_inventory_list.supply_id,
    quantity,
    quantity - IFNULL(deployed_quantity, 0) AS 'AVAILABLE QUANTITY',
    unit,
    item_name,
    supply_description,
    date_received,
    IFNULL(deployed_quantity, 0) AS 'deployed_quantity'
FROM supply_inventory_list 
LEFT JOIN supply_employee_list 
    ON supply_employee_list.`supply_id` = supply_inventory_list.`supply_id` 
WHERE item_name = 'LEGAL BOND PAPER'
ORDER BY date_received DESC

这是我得到的结果：

supply_id  quantity  AVAILABLE QUANTITY  unit    item_name         supply_description      date_received  deployed_quantity  
---------  --------  ------------------  ------  ----------------  ----------------------  -------------  -------------------
        3         5                   3  REAM    LEGAL BOND PAPER  HP LEGAL BOND PAPER     2019-08-30                       2
        3         5                   4  REAM    LEGAL BOND PAPER  HP LEGAL BOND PAPER     2019-08-30                       1
        1         5                   5  REAM    LEGAL BOND PAPER  CANON LEGAL BOND PAPER  2019-08-27                       0

这不是我要找的结果。我要根据 `deployed_quantity` 扣除的`数量`。这是我正在寻找的结果：

supply_id  quantity  AVAILABLE QUANTITY  unit    item_name         supply_description      date_received  deployed_quantity  
---------  --------  ------------------  ------  ----------------  ----------------------  -------------  -------------------
        3         5                   2  REAM    LEGAL BOND PAPER  HP LEGAL BOND PAPER     2019-08-30                       3
        1         5                   5  REAM    LEGAL BOND PAPER  CANON LEGAL BOND PAPER  2019-08-27                       0

如您所见，“可用数量”为 2，“已部署数量”为 3，因为第一个员工部署了 2 个数量，第二个员工部署了 1 个。

我如何产生这个结果？

标签： mysql

解决方案

您首先需要找到部署数量的总和，然后从库存数量中减去 -

SELECT SIL.supply_id,
       SIL.quantity,
       SIL.quantity - IFNULL(SEL.deployed_quantity, 0) AS 'AVAILABLE QUANTITY',
       SIL.unit,
       SIL.item_name,
       SIL.supply_description,
       SIL.date_received,
       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` 
WHERE SIL.item_name = 'LEGAL BOND PAPER'
ORDER BY SIL.date_received DESC

推荐阅读