如何使用 SQL 管理数据库中的 FIFO 队列？

Question

我的数据库中有两张表，一张用于In，一张用于Out。它们有两列：数量 和价格。如何编写选择正确价格的 SQL 查询？

例如：如果我有 3 个项目 in 为 75，然后有 3 个项目 in 为 80。那么我有两个 out 为 75，并且第三个出局应为 75 (X)，第四个出局应为 80 (Y)。

如何编写 X 和 Y 的价格查询？ 他们应该使用第三行和第四行的价格。例如，有什么方法可以选择 In 表中的第三行吗？我不能使用 auto_increment 作为“第三”行的标识符，因为这些表也将包含其他项目的帖子。这些行不会被删除，它们将出于责任原因而被保存。

SELECT Price FROM In WHERE ...?

NEW 数据库设计：

+----+
| In |
+----+------+-------+
| Supply_ID | Price |
+-----------+-------+
|     1     |  75   |
|     1     |  75   |
|     1     |  75   |
|     2     |  80   |
|     2     |  80   |
+-----------+-------+
+-----+
| Out |
+-----+-------+-------+
| Delivery_ID | Price |
+-------------+-------+
|      1      |  75   |
|      1      |  75   |
|      2      |   X   | <- ?
|      3      |   Y   | <- ?
+-------------+-------+

OLD 数据库设计：

+----+
| In |
+----+------+----------+-------+
| Supply_ID | Quantity | Price |
+-----------+----------+-------+
|     1     |  3       |  75   |
|     2     |  3       |  80   |
+-----------+----------+-------+

+-----+
| Out |
+-----+-------+----------+-------+
| Delivery_ID | Quantity | Price |
+-------------+----------+-------+
|      1      |  2       |  75   |
|      2      |  1       |   X   | <- ?
|      3      |  1       |   Y   | <- ?
+-------------+----------+-------+

Answer 1

阅读评论，您说您愿意添加自动增量或日期字段来了解每行的正确位置。添加此后，我建议在 In 表中再添加一行名为“processed”的行，当该行添加到表中时，该行会自动设置为 false。任何已复制到 OUT 的行都已将其已处理字段设置为 true。

+----+
| In |
+-----------+-----------+-------+-----------+
| AUtoId    | Supply_ID | Price | Processed |
+-----------+-----------+-------+-----------+
|     1     |     1     |  75   |     1     |
|     2     |     1     |  75   |     1     |
|     3     |     1     |  75   |     0     |
|     4     |     2     |  80   |     0     |
|     5     |     2     |  80   |     0     |
+-----------+-----------+-------+---------- +

然后，要找到下一个要移至 OUT 的项目，您可以执行以下操作：

SELECT TOP 1 Supply_ID, Price 
FROM In WHERE Processed = 0
ORDER BY [Your Auto Increment Field or Date]

一旦该行移至 OUT，您只需将该行的已处理字段更新为 true。

Answer 2

我看不到在这里有帮助的简单查询。为了在 SQL 中模拟 FIFO，我将查看三个表：OPERATION、OUT 和 FIFO。 OPERATION 实际上是事务的日志，而 FIFO 表是 FIFO 状态，而 OUT 是来自 FIFO 的响应。

您将在操作（添加和删除项目）进入 OPERATION 表时更新 CURRENT，并处理对 OUT 表中的项目“输出”的请求，减少 FIFO 中的值，并在必要时从 FIFO 表中删除记录。

即使如此，我也没有看到一个简单的查询来处理整个事情，因为需要查询第一条记录以查看每个操作是否有足够的数量，适当地更新该记录并查询无法执行该操作的其他记录实现了。我的 SQL 能力水平并不能引导我找到一个简单的解决方案，它对我来说构成了业务逻辑并被提升到该层。