sql server对数据进行先进先出

--> 测试数据：[m]
if object_id('[m]') is not null drop table [m]
go 
Create table m ( ID int not NULL , mCount int not NULL ,mTime datetime not null) 
INSERT into m(ID,mCount,mTime) 
SELECT 1,50,'2014-02-01' UNION ALL
SELECT 1,60,'2014-02-02' UNION ALL
SELECT 2,50,'2014-02-01' UNION ALL
SELECT 2,60,'2014-02-03' UNION ALL
SELECT 2,60,'2014-02-04' UNION ALL
SELECT 2,40,'2014-02-02' UNION ALL
SELECT 1,70,'2014-02-03' UNION ALL
SELECT 1,80,'2014-02-04' 
 
select * from m
/*
ID          mCount      mTime
----------- ----------- -----------------------
1           50          2014-02-01 00:00:00.000
1           60          2014-02-02 00:00:00.000
2           50          2014-02-01 00:00:00.000
2           60          2014-02-03 00:00:00.000
2           60          2014-02-04 00:00:00.000
2           40          2014-02-02 00:00:00.000
1           70          2014-02-03 00:00:00.000
1           80          2014-02-04 00:00:00.000
(8 行受影响)
*/
 
declare @i int 
set @i =80  --定义出货总数量
select a.id,a.mcount ,a.mtime,
case when a.mcount-(a.tmcount-@i) <0 then 0  when a.tmcount<@i then a.mcount else a.mcount-(a.tmcount-@i)end as '出货数量' 
from
(
select a.*,sum(isnull(b.mcount,0)) as Tmcount from m a left join m b on a.mtime>=b.mtime and a.id=b.id
group by a.id,a.mcount,a.mtime
)a 
/*
id          mcount      mtime                   出货数量
----------- ----------- ----------------------- -----------
1           50          2014-02-01 00:00:00.000 50
1           60          2014-02-02 00:00:00.000 30
1           70          2014-02-03 00:00:00.000 0
1           80          2014-02-04 00:00:00.000 0
2           40          2014-02-02 00:00:00.000 30
2           50          2014-02-01 00:00:00.000 50
2           60          2014-02-03 00:00:00.000 0
2           60          2014-02-04 00:00:00.000 0
(8 行受影响)

---------------------