来帮我看看这条统计语句怎么写？
表A
物品ID 数量
1 100
2 180
3 300
表B
物品ID 数量 批次ID
1 30 1
1 50 2
1 80 3
1 200 4
2 200 1
2 300 2
3 50 1
3 90 2
3 200 3
可以看做表A是出货表，表B是库存表，要求出货时按批次ID取货。
如物品1，要出货100，批次1取出30，不够，又取批次2的50，再取批次3的20。
查询结果按
物品ID 总数量 数量 批次ID
[解决办法]
--这個?
create table A(goodid int,value int)
insert into A
select 1,100 union all
select 2,180 union all
select 3,300
create table B(goodid int,value int ,id int)
insert into B
select 1,30,1 union all
select 1,50,2 union all
select 1,80,3 union all
select 1,200,4 union all
select 2,200,1 union all
select 2,300,2 union all
select 3,50,1 union all
select 3,90,2 union all
select 3,200,3
select 物品ID=A.goodid,
总数量=A.value,
case when isnull((select sum(value) from B where goodid=t.goodid and id <=t.id),0) <=A.value
then t.value
else
case when isnull((select sum(value) from B where goodid=t.goodid and id <t.id),0) <=A.value
then A.value- isnull((select sum(value) from B where goodid=t.goodid and id <t.id) ,0)
else 0
end
end as 数量 ,
批次ID=t.id
from A, B t
where A.goodid=t.goodid
order by A.goodid,t.id
/*
物品ID 总数量 数量 批次ID
----------- ----------- ----------- -----------
1 100 30 1
1 100 50 2
1 100 20 3
1 100 0 4
2 180 180 1
2 180 0 2
3 300 50 1
3 300 90 2
3 300 160 3
*/
drop table A,B
[解决办法]
--建立测试环境
create table #A(物品ID int,数量 int)
insert #A(物品ID,数量)
select '1 ', '100 ' union all
select '2 ', '180 ' union all
select '3 ', '300 '
go
create table #B(物品ID int,数量 int,批次ID int)
insert #B(物品ID,数量,批次ID)
select '1 ', '30 ', '1 ' union all
select '1 ', '50 ', '2 ' union all
select '1 ', '80 ', '3 ' union all
select '1 ', '200 ', '4 ' union all
select '2 ', '200 ', '1 ' union all
select '2 ', '300 ', '2 ' union all
select '3 ', '50 ', '1 ' union all
select '3 ', '90 ', '2 ' union all
select '3 ', '200 ', '3 '
go
--执行测试语句
select A.物品ID,A.数量 as 总数量,B.数量 as 现有数量,B.批次ID,0 as 数量
into #tmp
from #A A
join #B B on a.物品ID = b.物品ID
order by A.物品ID,B.批次ID
--select B.物品ID,B.数量,B.批次ID from #B B
declare @num int,@id int,@flag int
update #tmp
set @num = case when @id = 物品ID then @num-现有数量 else 总数量-现有数量 end
,@id = 物品ID
,数量 =
case
when @num > = 0 then 现有数量
when @num < 0 and @num+现有数量 > 0 then @num+现有数量
else 0
end
select 物品ID,总数量,批次ID,数量 from #tmp where 数量 > 0
go
--删除测试环境
drop table #A,#B,#tmp
go
/*--测试结果
物品ID 总数量 批次ID 数量
----------- ----------- ----------- -----------
1 100 1 30
1 100 2 50
1 100 3 20
2 180 1 180
3 300 1 50
3 300 2 90
3 300 3 160
(7 row(s) affected)
*/
[解决办法]
create table a(物品ID int,数量 int)
insert into a values(1, 100)
insert into a values(2, 180)
insert into a values(3, 300)
create table b(物品ID int,数量 int,批次ID int)
insert into b values(1, 30 , 1)
insert into b values(1, 50 , 2)
insert into b values(1, 80 , 3)
insert into b values(1, 200 , 4)
insert into b values(2, 200 , 1)
insert into b values(2, 300 , 2)
insert into b values(3, 50 , 1)
insert into b values(3, 90 , 2)
insert into b values(3, 200 , 3)
go
select b.物品ID , 总数量 = a.数量 , b.数量,b.批次ID from b,a,
(
select m.物品ID , min(m.批次ID) 批次ID from a,
(
select *,(select sum(数量) from b where 物品ID = t.物品ID and 批次ID <=t.批次ID) as '累计量 ' from b t
) m
where a.物品ID = m.物品ID and a.数量 <= m.累计量
group by m.物品ID
) t
where b.物品ID = t.物品ID and b.批次ID <= t.批次ID and b.物品ID = a.物品ID
drop table a,b
/*
物品ID 总数量 数量 批次ID
----------- ----------- ----------- -----------
1 100 30 1
1 100 50 2
1 100 80 3
2 180 200 1
3 300 50 1
3 300 90 2
3 300 200 3
（所影响的行数为 7 行）
*/
