如A:
ID Name QuantityA QuantityB QuantityC
1 A 100(表A) 0 100
2 A 100(QC) 100 200
3 A 200 100 100
同一名称时第一条记录为表A中的Quantity,以后则为上一条的QuantityC
ID Name QuantityA QuantityB QuantityC
1 A 100(表A) 0 100
2 A 100(QC) 100 200
3 A 200 100 100
同一名称时第一条记录为表A中的Quantity,以后则为上一条的QuantityC
select a.id,a.name,a.Quantity as QuantityA,isnull(b.Quantity,0) as QuantityB, QuantityC=(case b.ant when 'in' then (a.Quantity + b.Quantity) else (a.Quantity + b.Quantity) end) from a left join b on a.id=b.id
from(
select sid=0,id,name
,QuantityA=Quantity
,QuantityB=0
,QuantityC=Quantity
from 表A
union all
select sid=1,b.id,b.name
,QuantityA=isnull((
select sum(case Ant when 'In' then Quantity else -Quantity end)
from 表B where name=a.name and id<a.id)
,0)+b.Quantity
,QuantityB=isnull(a.Quantity,0)
,QuantityC=isnull((
select sum(case Ant when 'In' then Quantity else -Quantity end)
from 表B where name=a.name and id<=a.id)
,0)+b.Quantity
from 表B a
right join 表A b on a.Name=b.Name
)a order by name,sid
create table 表A(ID int,Name char(1),Quantity int)
insert 表A select '1','A','100'
union all select '2','B','150'
union all select '3','C','200'create table 表B(ID int,Name char(1),Quantity int,Ant varchar(10))
insert 表B select '1','A','100','In'
union all select '2','B','200','In'
union all select '3','C','300','In'
union all select '4','C','200','Out'
union all select '5','B','100','Out'
union all select '6','A','100','Out'
go--查询
select id,name,QuantityA,QuantityB,Quantityc
from(
select sid=0,id,name
,QuantityA=Quantity
,QuantityB=0
,QuantityC=Quantity
from 表A
union all
select sid=1,b.id,b.name
,QuantityA=isnull((
select sum(case Ant when 'In' then Quantity else -Quantity end)
from 表B where name=a.name and id<a.id)
,0)+b.Quantity
,QuantityB=isnull(a.Quantity,0)
,QuantityC=isnull((
select sum(case Ant when 'In' then Quantity else -Quantity end)
from 表B where name=a.name and id<=a.id)
,0)+b.Quantity
from 表B a
right join 表A b on a.Name=b.Name
)a order by name,sid
go--删除测试
drop table 表a,表b/*--测试结果id name QuantityA QuantityB Quantityc
----------- ---- ----------- ----------- -----------
1 A 100 0 100
1 A 100 100 200
1 A 200 100 100
2 B 150 0 150
2 B 150 200 350
2 B 350 100 250
3 C 200 0 200
3 C 200 300 500
3 C 500 200 300(所影响的行数为 9 行)
--*/
into 表C
from(
select sid=0,id,name
,QuantityA=Quantity
,QuantityB=0
,QuantityC=Quantity
from 表A
union all
select sid=1,b.id,b.name
,QuantityA=isnull((
select sum(case Ant when 'In' then Quantity else -Quantity end)
from 表B where name=a.name and id<a.id)
,0)+b.Quantity
,QuantityB=isnull(a.Quantity,0)
,QuantityC=isnull((
select sum(case Ant when 'In' then Quantity else -Quantity end)
from 表B where name=a.name and id<=a.id)
,0)+b.Quantity
from 表B a
right join 表A b on a.Name=b.Name
)a order by name,sid
(Select Name,Quantity as QuantityA,0 as QuantityB,cast('In' as varchar(3)) Ant, 0 ord from ta
Union Select Name,IsNull((Select sum(Case When Ant = 'In' Then Quantity Else -Quantity End) from #tmp where Name = A.Name and ID < A.ID),0)
+ (Select Quantity from ta where Name = A.Name),Quantity,Ant,ID from #tmp A
) C order by Name,Ant,ordDrop Table #tmp
Select ID = identity(int,1,1),Name,Quantity,Ant into #tmp from tb order by Name,AntSelect Name,QuantityA,QuantityB,QuantityA+(Case when Ant = 'In' Then QuantityB Else -QuantityB End) as QuantityC from
(Select Name,Quantity as QuantityA,0 as QuantityB,cast('In' as varchar(3)) Ant, 0 ord from ta
Union Select Name,IsNull((Select sum(Case When Ant = 'In' Then Quantity Else -Quantity End) from #tmp where Name = A.Name and ID < A.ID),0)
+ (Select Quantity from ta where Name = A.Name),Quantity,Ant,ID from #tmp A
) C order by Name,Ant,ordDrop Table #tmp
Name QuantityA QuantityB QuantityC
---------- ----------- ----------- -----------
A 100 0 100
A 100 100 200
A 200 100 100
B 150 0 150
B 150 200 350
B 350 100 250
C 200 0 200
C 200 300 500
C 500 200 300 9 Row(s) affected