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

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

1               100
2               180
3               300

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

------解决方案--------------------
--这個?
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,

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 数量 ,

from A, B t
where A.goodid=t.goodid
order by A.goodid,t.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