mysql查询列出所有带有外键的项目以了解库存位置

Question

我有一个用于管理多个位置的商品库存的数据库。我有 3 个表，例如:

table_items
+----------+-------------+
| id_items |     name    |
+----------+-------------+
|  1       |   item 1    |
|  2       |   item 2    |
+----------+-------------+

table_location
+-------------+-------------+
| id_location |     name    |
+-------------+-------------+
|  1          |   location 1|
|  2          |   location 2|
+-------------+-------------+

table_stock
+-------------+-----------+----------+
| id_location |  id_item  |   stock  |
+-------------+-----------+----------+
|  1          |   1       |     3    |
|  1          |   2       |     0    |
|  2          |   1       |     1    |
+-------------+-----------+----------+

现在我如何列出每个位置的所有项目，包括那里不存在的项目。例如我想获取位置 2 中的项目，因此结果将是:

+-------------+-----------+----------+
| id_location |  id_item  |   stock  |
+-------------+-----------+----------+
|  2          |   1       |     1    |
|  2          |   2       |   null   |
+-------------+-----------+----------+

或者也许还有其他方法可以做这样的事情..？

Answer 1

尝试这个查询:

SELECT s.id_location, i.id_items, CASE WHEN sum(stock)=0 THEN null ELSE sum(stock) END AS stock
FROM table_items i LEFT JOIN table_stock s ON i.id_items=s.id_items
WHERE s.id_location=2
GROUP BY s.id_location, i.id_items;

这将为您提供位置和商品的总库存。希望这能解决您的问题。