使用FIFO发出物料 [英] issue an item using FIFO

问题描述

我正在使用php和mysql设计基于Web的库存控制系统.
我在数据库中有两个表，分别是接收项目表和发货项目表
这是表结构
接受禁忌
项目编号日期接收接收编号QuntityReceive.
xxx 2010-01-01 c12 10
yyy 2010-02-01 c15 20
xxx 2010-05-05 c34` 20
问题表
ItemCode日期发行IssueNumber数量发行
xxx 2010-03-03 c12 1
yyy 2010-02-01 c15 3
xxx 2010-05-05 c34` 6

并且我想使用FIFO方法(先进先出方法)发布项目，所以请帮助我如何编写SQL查询

I am designing web based stock control system using php and mysql.
I have two table in Database which is Receive item and issue item table
and here is the table structure
Receive tabale
Item Code Date Receive Receive number QuntityReceive.
xxx 2010-01-01 c12 10
yyy 2010-02-01 c15 20
xxx 2010-05-05 c34` 20
issue Table
ItemCode Date issue IssueNumber Quantity Issue
xxx 2010-03-03 c12 1
yyy 2010-02-01 c15 3
xxx 2010-05-05 c34` 6

and I want to issu item with FIFO Method ( first in first out Method) so please help me how to write SQL query

推荐答案

在您的表中具有生成的自动增量键并将其称为"GenID".然后很简单:

Have a generated autoincrement key in your tables and lets call it "GenID". Then it''s easy:

SELECT * FROM IssueTable WHERE GenID = (SELECT MIN(GenID) FROM IssueTable)

其他表也一样:

Same for the other table:

SELECT * FROM ReceiveTable WHERE GenID = (SELECT MIN(GenID) FROM ReceiveTable)

如果您完成了对所获取记录的处理，请从相应的表中删除该条目，并在删除操作上触发该记录，以将该记录的副本复制到某个事务历史记录表中.通过选择具有最小GenID的记录，您将始终在FIFO模式之后获取记录.

(我的第一个方法是使用DateTime，但是如果以非常连续的顺序插入记录，dateteime的精度可能不够.)

最好的问候，

If you''re done with the processing of the fetched record delete that entry from the correspoding table and have a trigger on delete make a copy of that record to some transaction history table. By selecting the record with the smallest GenID you''ll alway fetch records after the FIFO pattern.

(My first thouhgt was to use DateTime, but if the records are inserted in very fast succession the precision of dateteime might not be adequate.)

Best Regards,

这篇关于使用FIFO发出物料的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！