# 来帮小弟我看看这条统计语句如何写(2)

www.myexceptions.net  网友分享于：2013-03-08  浏览：5次

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
/*--测试结果

----------- ----------- ----------- -----------
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

/*

----------- ----------- ----------- -----------
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 行）
*/