尝试了解有关子查询的更多信息。我正在寻找一种方法来减去和比较两个表。
- 存货
- Sales
我的数据记录如下：
存货：
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)
Sales
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 |
+----------+------------+-----------+-------+-----------+
您可以使用查询结果并将它们结合起来计算每种产品的剩余数量
SELECT
a.store_id,
a.product_id,
a.inventory,
b.sales,
a.inventory - b.sales AS remaining
FROM (
SELECT store_id, product_id, COALESCE(SUM(quantity),0) AS inventory
FROM inventories WHERE store_id = 1
GROUP BY product_id) a
LEFT JOIN (
SELECT store_id, product_id, COALESCE(SUM(quantity),0) AS sales
FROM sales WHERE store_id = 1
GROUP BY product_id ) b USING(store_id, product_id)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
