试图了解更多关于子查询的信息。我正在寻找一种方法来减去和比较两个表。
我的数据记录如下:
库存:
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)
获得以下结果的正确子查询是什么?
+----------+------------+-----------+-------+-----------+
| 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
字段。这是一个查询以获取您所描述的内容:
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
)您可以从
sales.id
子句中排除 select
,但需要将其保留在 group by
和 order by
中。关于mysql - 如何使用mysql子查询减去库存和销售?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28436485/