如何使用 mysql 子查询减去库存和销售?

How to subtract inventory and sale using mysql subquery?(如何使用 mysql 子查询减去库存和销售?)

问题描述

尝试了解有关子查询的更多信息.我正在寻找一种方法来减去和比较两个表.

Trying to learn more on sub-query. I am looking for a way to subtract and compare two tables.

1. 库存
2. 销售

我的数据记录如下:

库存:

mysql> select store_id, product_id, sum(quantity) as inventory from inventories where store_id = 1 group by product_id;
+----------+------------+-----------+
| store_id | product_id | inventory |
+----------+------------+-----------+
|        1 |          8 |        24 |
|        1 |         10 |         4 |
|        1 |         14 |        24 |
+----------+------------+-----------+
3 rows in set (0.00 sec)

销售

mysql> select store_id, product_id, sum(quantity) as sales from sales where store_id = 1 group by product_id;
+----------+------------+-------+
| store_id | product_id | sales |
+----------+------------+-------+
|        1 |          8 |    12 |
|        1 |         14 |     2 |
|        1 |          8 |     1 |
+----------+------------+-------+
2 rows in set (0.00 sec)

获得以下结果的正确子查询是什么?

What is the proper sub-query to have the following result?

+----------+------------+-----------+-------+-----------+
| store_id | product_id | inventory | sales | remaining |
+----------+------------+-----------+-------+-----------+
|        1 |          8 |        24 |    12 |        12 |
|        1 |         14 |        24 |     2 |        22 |
|        1 |          8 |        12 |     1 |        11 |
+----------+------------+-----------+-------+-----------+

推荐答案

要实现所需的输出，您需要计算产品销售的运行总计.为了获得有意义的数据，sales 表中的数据必须按时间顺序排列.因此，您至少需要一个字段来对数据进行排序——不管是时间戳还是 id 字段.假设 sales 表中有一个 id 字段.这是获取您所描述内容的查询:

To achieve the desired output you need to calculate running totals of product sales. To get meaningful data, the data in sales table must be ordered chronologically. So you need at least one more field to sort data - it doesn't matter if it's a timestamp, or id field. Let's assume there is an id field in sales table. This is a query to get what you described:

SELECT 
    sales.id,
    sales.store_id,
    sales.product_id,
    inventories.quantity-IFNULL(SUM(sales_2.quantity), 0) as inventory,
    sales.quantity as sales,
    inventories.quantity-IFNULL(SUM(sales_2.quantity), 0) - sales.quantity as remaining
FROM
    sales
        INNER JOIN
    inventories ON inventories.store_id = sales.store_id
        AND inventories.product_id = sales.product_id
        LEFT JOIN
    sales AS sales_2 ON sales_2.store_id = sales.store_id
        AND sales_2.product_id = sales.product_id
        AND sales_2.id < sales.id
GROUP BY sales.id , sales.store_id , sales.product_id
ORDER BY sales.id

sales 表的第二个实例名为 sales_2 用于计算早期销售额的总和 (sales_2.id)