用于计算“保留”库存项目的SQL查询

html5 • • 问答

我们正在为名为 readoutprobes 和 readoutprobekits 的项目创建一个库存系统。下面的模式使用items和itemkits两个词进行了简化。

一个物品包，是一个或多个物品的预定义集合，即一个套件。在套件中，特定类型的物品只能出现一次。一个套件，通常包含约 40 件物品。套件中物品的定义由itemkit_item表捕获。套件的库存记录在itemkit_containers表中。

itemkit_container不跟踪物理项目容器。相反，它假设使用一组物理项目正确地“组装”了一个物理项目包，但我们不知道是哪些。填充时，itemkit_containers 记录中的“填充”字段设置为 true。

物品的库存由item_containers表跟踪。它的存在由容器体积监控。当体积为 0 时，容器被认为是空的。

从item_container表中获取特定物品的体积> 0的物理物品容器的数量，对于套件也是如此

我们希望获得每个项目的“保留计数”编号，以反映套件库存。

例如，假设我们有一个名为 A 的项目，其计数为 42。如果我们正在创建一个包含名为 A 的项目和相应的 itemkit_container 的 itemkit，我们希望“reserved”的计数为 1，对于 item一种。

项目的“主查询”如下所示：

SELECT items.*,         
    ic.item_count
FROM items
LEFT JOIN (
    SELECT p.id, COUNT(*) item_count, ic.item_id
    FROM  items AS p, item_containers AS ic
    WHERE p.id = ic.item_id AND ic.volume > 0
    GROUP BY p.id
    ) AS ic   
    ON ic.item_id = items.id        
GROUP BY items.id    
ORDER BY items.id;

项目表中的数据：

item_containers 表中的数据：

itemkits 表中的数据：

itemkit_item 表中的数据：

itemkit_containers 中的数据：

可以观察到，物品包及其库存的唯一记录包含物品 ID = {1,3}

这个问题是为了找出在任何一个时间点如何查询“空闲”（或保留）物理项目的数量，即 item_containers 存货。

上面的查询，返回这个结果：

我们需要一个额外的字段，指示每个项目的“保留”计数，反映项目和项目包的实际库存状态。

对于上面的数据，这将是

A -> Reserved = 1
B -> Reserved = 0
C -> Reserved = 1
D -> Reserved = 0

创建和填充上述表格的 db fiddle 在这里：
DB Fiddle

我们使用的是 MySQL 8.0。

注意：下面的答案接近正确。但是，它不会将 item_containers（实际库存）与 itemkit_container 记录相关联，而是与 itemkit 记录相关联。通过将 itemkit_containers 表中的填充字段切换为“0”，这一点变得清晰。IE：

即使套件不再填充，输出也会显示相同的“保留”计数。在这种情况下，保留应等于“0”。这是这种情况的小提琴：Fiddle where Reserved should be all '0'

以上是用于计算“保留”库存项目的SQL查询的全部内容。
THE END
分享
二维码
< <上一篇
下一篇>>