求数量分批SQL

[wdsimon]

表table1数据如下
ITEM 日期 计划生产量 完成总量
A001 2006-11-1 1000 2500
A001 2006-11-8 1000 2500
A001 2006-11-22 1500 2500

查询需得到如下结果
ITEM 日期 计划生产量 已完成量
A001 2006-11-1 1000 1000
A001 2006-11-8 1000 1000
A001 2006-11-22 1500 500

就是要把总量按日期先后分配到每天.

[xluzhong]

----稍微改动一点
--语句
select item,date,plancount,
case when finishcount - sum1 >= plancount
then plancount
else case when finishcount > sum1
then finishcount - sum1
else 0
end
end as finish
from
(select *,sum1 = (select isnull(sum(plancount),0)
from table1 where item = t1.item and date < t1.date)
from table1 t1)a

[wdsimon]

UP

[冷箫轻笛]

--建表
create table table1
(
item varchar(4),
date datetime,
plancount int,
finishcount int
)

insert into table1 select 'A001', '2006-11-1', 1000, 2500
insert into table1 select 'A001', '2006-11-8', 1000, 2500
insert into table1 select 'A001', '2006-11-22', 1500, 2500

--语句
select item,date,plancount,
case when finishcount - sum1 >= plancount
then plancount
else case when finishcount > sum1
then finishcount - sum1
else 0
end
end as finish
from
(select *,sum1 = (select isnull(sum(plancount),0) from table1 where date < t1.date)
from table1 t1)a

--结果
A001 2006-11-01 00:00:00.000 1000 1000
A001 2006-11-08 00:00:00.000 1000 1000
A001 2006-11-22 00:00:00.000 1500 500