我正在努力为与我合作的企业设计一个资产管理系统。他们在居家办公环境中为员工工作。
我已经开始了数据库设计，但我不确定如何获得当前库存的数字。例如，我知道描述 = "鼠标"的资产总数为 (3)，但 1 被发送到 John Doe，1 最初被发送给 Jane Doe，但它被退回，因为它被破坏了。所以我还有 1 只鼠标要发送给简。
要使用什么 SQL 查询才能获得当前存储在存储中的"鼠标"资产的总量？
我认为它是
[总库存(鼠标)减去带(鼠标)的出货量] + [退回的(鼠标)的退货货件数 = "1" 减去退役的库存(鼠标)数量 = "1"]
这是我所在的地方：
Tables:
Employees
ID (Primary key, incremental)
F_Name
L_Name
Address
Phone_Number
+----+---------+----------+-----------------+--------------------------
| ID | F_Name | L_Name | Address | Phone_Number
| 122 John Smith 123 anywhere... 111-222-3333
| 123 John Doe 128 somewhere... 222-333-4444
| 135 Jane Doe 128 somewhere... 222-333-4444
Shipments_Outbound
ID (Primary key, incremental)
Employee_ID (Foreign key, references Employees(ID))
Sent (date value for date left warehouse)
Courier_ID
+----+--------------+------------+---------------------------------
| ID | Employee_ID | Sent | Courier_ID
| 001 123 2016-03-12 2223334445
| 002 135 2017-03-12 3334445556
Contents_Outbound (many to many composite from Shipments_Outbound and Assets)
Shipment_ID (Primary key, references Shipments_Outbound(ID))
Asset_ID (Primary key, references Assets(ID)
+------------+-----------
| Shipment_ID| Asset_ID
| 001 001
| 001 004
| 001 005
| 002 002
| 002 003
| 002 006
Assets
ID (Primary key, incremental)
SKU (unique)
Description
Decommissioned (tinyint value for discarded equipment)
Comments (to note why discarded)
+----+---------+---------------+-----------------+-------------
| ID | SKU | Description | Decommissioned | Comments
|001 123456 Monitor 0
|002 987654 Monitor 0
|003 456789 Desktop 0
|004 NULL Mouse 0
|005 NULL Keyboard 0
|006 NULL Mouse 1 Broken scroll wheel
|007 NULL Mouse 0
Shipments_Return
ID (Primary key, incremental)
Employee_ID (Foreign key, references (Employees(ID))
Issued (Date value for when return waybill issued for employee)
Courier_ID
Returned (tinyint for boolean on if product was returned)
+----+---------+---------------+-----------------+-------------
| ID | Employee_Id | Issued | Courier_ID | Returned
|222 123 2018-01-12 9998887776 0
|223 135 2018-01-13 8887776665 1
Contents_Return (many to many composite from Shipments_Return and Assets)
ShipmentR_ID (Primary key, references Shipments_Return(ID))
Asset_ID (Primary key, references Assets(ID)
+-------------+-----------
| ShipmentR_ID| Asset_ID
| 222 001
| 222 004
| 223 002
| 223 006
你可以这样做你的查询。但是，在其他情况下，您可能会遇到问题。
您有一个小部件。您的查询会说 Instock 说总计 1 - 发货 (0) + 退货 (0) - 退役 (0) = 1。好
您将其运送给工作人员。库存 说总计 1 - 发货 1 + 退货 0 - 退役 0 = 0。 好
如果工人的房子被抢劫并且小部件消失了会怎样？或者他们有火。或者小部件永远不会首先到达工作人员。如果您只是停用它，您将获得总计 1 - 已发货 1 + 退货 0 - 退役 1 = -1。如果你不退役，那么据你所知，它总是在工人的地方，这是不正确的。你不会有回报，他们不能退货。您必须编造虚假退货才能获得正确的数字。
您需要有一个查询，在每一步都忽略已停用的资产，例如
total stock(Mouse) where decommissioned = 0
minus shipments out with (Mouse) where decommissioned = 0
plus return shipments with (Mouse) where returned = "1" and decommissioned = 0
或者，您可以在资产上添加一个状态列，链接到具有"有货"、"使用中"、"已退役"等值的状态表。创建时，资产将设置为"有货"。装运完成后，装运中的所有资产都将更改为"使用中"。在退货时(当返回设置为 1 时)，退货中的所有资产将更改为"有货"。停用后，将状态设置为已停用等。如果您决定需要，您可以为其他情况添加其他状态值，例如"正在修复"，"正在退回"。
然后你在上面查询会像
Total(Mouse) where Status = <in-stock status>