如何使用 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
The second instance of sales
table called sales_2
is used to calculate the sum of earlier sales (sales_2.id<sales.id
)
您可以将 sales.id
从 select
子句中排除,但您需要将其保留在 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
.
相关文章