mysql - 如何通过计算IN和OUT之差来计算物理量

Question

我正在开发一个库存控制应用程序，我正在使用 MySQL RDBMS 和 InnoDB 引擎，目前我想生成一个报告，通过计算 IN_OUT 列的 SUM 之间的差异来计算产品的物理数量一个 MySQL 查询。

我在 in_out_products 中使用值“1”作为 IN，“0”作为 OUT

所以我需要一个区分 IN 和 OUT 的列
每种产品的IN - OUT =物理数量

以更清楚的方式，我的意思是 SUM(quantity) where in_out=1 和 SUM(quantity) where in_out=0 ，in_out 列在 in_out_products 表中的区别

第一个表

select * from products;


+------------+--------------+-------------+----------------+-------+------------+
| product_id | product_code | part_number | product_name   | brand | location   |
+------------+--------------+-------------+----------------+-------+------------+
|          1 | 100100       | 100200      | RICHO maginate | RICHO | location 4 |
|          2 | 1112211      | 100300      | RICHO Cyan     | RICHO | location 4 |
|          3 | 111333       | 100400      | RICHO Yellow   | RICHO | location 4 |
+------------+--------------+-------------+----------------+-------+------------+

第二张 table

+-----------+------------+--------------+-------------+----------+--------+
| record_id | product_id | product_code | part_number | quantity | in_out |
+-----------+------------+--------------+-------------+----------+--------+
|         1 |          1 | 100100       | 100200      |       10 |      1 |
|         2 |          1 | 100100       | 100200      |       10 |      1 |
|         3 |          1 | 100100       | 100200      |       30 |      1 |
|         4 |          2 | 1112211      | 100300      |       10 |      1 |
|         5 |          2 | 1112211      | 100300      |       10 |      1 |
|         6 |          2 | 1112211      | 100300      |       10 |      1 |
|         7 |          2 | 1112211      | 100300      |       10 |      1 |
|         8 |          3 | 111333       | 100400      |       10 |      1 |
|         9 |          3 | 111333       | 100400      |       10 |      1 |
|        10 |          3 | 111333       | 100400      |       10 |      1 |
+-----------+------------+--------------+-------------+----------+--------+

现在我想创建一个 SQL 查询来显示当前数量的所有产品，我没有做到这一点，到目前为止我设法通过这个查询获得了 IN 数量。

计算每个产品的 IN 数量的 SQL 查询:

select p.product_id,
       p.product_id,
       p.product_code,
       p.product_name,
       SUM(io.quantity) as physical_quantity
       from products as p,
       in_out_products as io where p.product_id=io.product_id and io.deleted_at is null and in_out=1 group by p.product_id;

结果

+------------+------------+------------+-- --------------+----------------+
|产品编号 |产品编号 |产品代码 |产品名称 |物理量 |
+------------+------------+------------+-------- ------+--------------------+
| 1 | 1 | 100100 | RICHO 奇幻 | 50 |
| 2 | 2 | 1112211 | RICHO 青色 | 40 |
| 3 | 3 | 111333 | RICHO 黄色 | 30 |
+------------+------------+------------+-------- ------+--------------------+


Answer 1

如果没记错你需要使用Conditional Aggregate

select p.product_id,
       p.product_id,
       p.product_code,
       p.product_name,
       SUM(io.quantity) as physical_quantity,
       SUM(case when in_out=1 then io.quantity else 0 END) - 
       SUM(case when in_out=0 then io.quantity else 0 END) AS Difference
from products as p 
Join in_out_products as io 
on p.product_id=io.product_id 
Where io.deleted_at is null 
and in_out=1 
group by p.product_id;