在SQL中使用FIFO更新事务记录

Question

我有一个接收和发货的申请。我在transaction表中处理这两个进程。在接收物品时手动给出京东方编号，因此所有的接收记录都有京东方编号。但是，当运送一个项目时，我没有更新这个BOE列。实际上，我需要知道这个项目来自哪个京东方(我想使用先进先出)的标准。现在我的transaction表有很多数据。但我想知道每个京东方有多少项目来了，有多少项目出来了。

我做过这样的查询

select 
    F_lot_number, sum(INq) 'IN', sum(OUTs) 'OUT', sum(BOE) 'BOEcount' 
from 
    (select 
         F_lot_number,
         case 
            when F_Stock_Type = 'IN' then sum(F_qty) 
         end as 'INq',
         case 
            when F_Stock_Type = 'out' and  F_IsBook = '2' then sum(F_qty)  
         end as 'OUTs',
         case 
            when F_Stock_Type = 'IN' 
               then count(distinct F_BillOf_entryNumber) 
         end as 'BOE'
     from 
         T_Tra_Transaction 
     group by 
         F_lot_number, F_Stock_Type, F_IsBook) d 
group by 
    F_lot_number

输出如下所示：

​

​

if示例

select 
    F_QTY, F_lot_number, F_BillOf_entryNumber 
from 
    T_Tra_Transaction 
where 
    F_lot_number = '150723A151' 
    and F_Stock_Type = 'IN'

select 
    F_QTY, F_lot_number, F_BillOf_entryNumber 
from 
    T_Tra_Transaction 
where 
    F_lot_number = '150723A151' 
    and F_Stock_Type = 'out'

输出如下所示：

​

​

我需要用相应的京东方编号更新所有的状态。我该怎么做呢？我需要更新我所有现有的记录，根据that.if的任何帮助是非常感谢

在我的表中，transdatetime字段也在那里。

我已经查过其他批号了..

select 
    F_QTY, F_lot_number, F_BillOf_entryNumber 
from 
    T_Tra_Transaction 
where 
    F_lot_number = '150919C131' 
    and F_Stock_Type = 'IN'

select 
    F_QTY, F_lot_number, F_BillOf_entryNumber 
from 
    T_Tra_Transaction 
where 
    F_lot_number = '150919C131' 
    and F_Stock_Type = 'out'

但我是这样放出来的：

​

Answer 1

我假设每一批都是以收购时的相同数量出售的。所以这个想法就是正确地row_number()输入和输出，并从相应的ins中更新输出。未测试。

update tout
set F_BillOf_entryNumber = tin.F_BillOf_entryNumber 
from (
    select F_QTY, F_lot_number, F_BillOf_entryNumber 
    , rn = row_number() over (partition by F_QTY, F_lot_number order by transdatetime desc)
    from 
        T_Tra_Transaction 
    where 
        F_Stock_Type = 'out'
    ) tout
join (
    select 
        F_QTY, F_lot_number, F_BillOf_entryNumber
        , rn = row_number() over (partition by F_QTY, F_lot_number order by transdatetime desc)
    from 
        T_Tra_Transaction 
    where 
        F_Stock_Type = 'IN'
    ) tin 
    on tout.F_QTY = tin.F_QTY
       and tout.F_lot_number = tin.F_lot_number
       and tout.rn = tin.rn;

编辑

我在样本数据上进行了测试。

create table T_Tra_Transaction(
F_QTY int, F_lot_number int , F_BillOf_entryNumber int,
     transdatetime int, F_Stock_Type varchar(3));
insert  T_Tra_Transaction (F_QTY, F_lot_number, F_BillOf_entryNumber,transdatetime,F_Stock_Type)
values
(10,1,101,200,'IN'),
(20,1,102,201,'IN'),
(20,1,null,200,'out');