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

问题描述：

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

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

库存
销售

我的数据记录如下:

库存:

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< sales.id )

The second instance of sales table called sales_2 is used to calculate the sum of earlier sales (sales_2.id<sales.id)

您可以从 select 子句中排除 sales.id ，但是您需要将其保留在 group by 和 order by.

You can exclude sales.id from the select clause, but you need to keep it in group by and order by.

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

相关推荐