高手请进 (库存批次问题)

Question

库存表
GoodsCode   Num   InDate
001          10    2008-03-01  
001          16    2008-03-02 
001          29    2008-03-03销售了30个库存之后
库存表
GoodsCode   Num   InDate
001          0     2008-03-01  
001          0     2008-03-02 
001          25    2008-03-03怎样写 Select 语句,  按日期销售(先进先出)

Answer 1

declare @t table(ID int,Date datetime,change int)
insert into @t select 1,'2005-01-01',10
insert into @t select 2,'2005-01-02',20
insert into @t select 3,'2005-01-03',-10declare @n int 
set @n=100select
    ID,Date,change,结存=(@n-isnull((select sum(change) from @t where Date>a.Date and id>a.id),0))
from
    @t a
/*
ID          Date                                                   change      结存          
----------- ------------------------------------------------------ ----------- ----------- 
1           2005-01-01 00:00:00.000                                10          90
2           2005-01-02 00:00:00.000                                20          110
3           2005-01-03 00:00:00.000                                -10         100（所影响的行数为 3 行）*/

Answer 2

declare @t table(GoodsCode varchar(10),   Num  int, InDate datetime)insert into @t select '001',10,'2008-03-01'   
insert into @t select '001',16,'2008-03-02'  
insert into @t select '001',29,'2008-03-03' declare @n int 
set @n=30select
    GoodsCode,InDate,Num=case when (@n>isnull((select sum(Num) from @t where InDate<=a.InDate),0)) then 0 
                              else (isnull((select sum(Num) from @t where InDate<=a.InDate),0)-@n) end
from
    @t a
order by indate
/*
GoodsCode  InDate                                                 Num         
---------- ------------------------------------------------------ ----------- 
001        2008-03-01 00:00:00.000                                0
001        2008-03-02 00:00:00.000                                0
001        2008-03-03 00:00:00.000                                25（所影响的行数为 3 行）*/

Answer 3

declare @t table(GoodsCode varchar(10),   Num  int, InDate datetime)insert into @t select '001',10,'2008-03-01'   
insert into @t select '001',16,'2008-03-02'  
insert into @t select '001',29,'2008-03-03' 
insert into @t select '001',29,'2008-03-04' 
declare @n int 
set @n=30select
    GoodsCode,InDate,Num=case when (@n < isnull((select sum(Num) from @t where InDate<a.InDate),0)) then num
                         else
                         case when (@n>isnull((select sum(Num) from @t where InDate<=a.InDate),0))
                              then 0 
                              else (isnull((select sum(Num) from @t where InDate<=a.InDate),0)-@n) end
                         end
from
    @t a
order by indate
/*
GoodsCode  InDate                                                 Num         
---------- ------------------------------------------------------ ----------- 
001        2008-03-01 00:00:00.000                                0
001        2008-03-02 00:00:00.000                                0
001        2008-03-03 00:00:00.000                                25
001        2008-03-04 00:00:00.000                                29（所影响的行数为 4 行）
*/

Answer 4

关键你要让用select 
要是用update 呢

Answer 5

declare @t table(GoodsCode varchar(10),   Num  int, InDate datetime)insert into @t select '001',10,'2008-03-01'   
insert into @t select '001',16,'2008-03-02'  
insert into @t select '001',29,'2008-03-03' 
insert into @t select '001',29,'2008-03-04' 
declare @n int 
set @n=30update a
set num = case when @n > 0 then 0
               else
                   case when -@n < num then - @n 
                        else num end
               end,
    @n = @n - num
from @t a
select * from @t
/*
GoodsCode  Num         InDate                                                 
---------- ----------- ------------------------------------------------------ 
001        0           2008-03-01 00:00:00.000
001        0           2008-03-02 00:00:00.000
001        25          2008-03-03 00:00:00.000
001        29          2008-03-04 00:00:00.000（所影响的行数为 4 行）
*/