declare @A table(idNum int , Qnty int)
insert into @A values(1,10)
insert into @A values(1,15)
insert into @A values(2,50)
insert into @A values(2,10)
declare @B table(idNum int , Qnty int )
insert into @B values(1,5)
insert into @B values(1,6)
insert into @B values(2,40)
insert into @B values(2,10)declare @C table(idNum int , Qnty int , tempQnty int ,lastQnty int )
--insert into @C values(1,10,10,0)
--insert into @C values(1,15,1,14)
--insert into @C values(2,50,50,0)declare @idnum int,@qnty int,@bSum int,@bLost int,@flagId int
select @flagId=0
declare my_cursor cursor for
select idnum,qnty from @a
open my_cursor
fetch next from my_cursor into @idnum,@qnty
while @@fetch_status=0
begin
if @idNum<>@flagId
begin
select @bSum=case when (select isnull(sum(tempQnty),0) from @c where idnum=@idnum)>0
then (select sum(qnty) from @b where idnum=@idnum)-(select sum(tempQnty) from @c where idnum=@idnum)
else (select sum(Qnty) from @b where idnum=@idnum)
end
if @qnty-@bSum>=0 --已经减完
begin
insert into @c(idnum,qnty,tempqnty,lastqnty)
select @idnum,@qnty,@bSum,@qnty-@bSum
select @flagId=@idNum
end
else
begin
insert into @c(idnum,qnty,tempqnty,lastqnty)
select @idnum,@qnty,@qnty,0
select @flagId = 0
end
end
fetch next from my_cursor into @idnum,@qnty
end
close my_cursor
deallocate my_cursor
select * from @c
/*
结果:
idnum qnty tempqnty lastqnty
---------------------------------------------
1 10 10 0
1 15 1 14
2 50 50 0*/
insert into @A values(1,10)
insert into @A values(1,15)
insert into @A values(2,50)
insert into @A values(2,10)
declare @B table(idNum int , Qnty int )
insert into @B values(1,5)
insert into @B values(1,6)
insert into @B values(2,40)
insert into @B values(2,10)declare @C table(idNum int , Qnty int , tempQnty int ,lastQnty int )
--insert into @C values(1,10,10,0)
--insert into @C values(1,15,1,14)
--insert into @C values(2,50,50,0)declare @idnum int,@qnty int,@bSum int,@bLost int,@flagId int
select @flagId=0
declare my_cursor cursor for
select idnum,qnty from @a
open my_cursor
fetch next from my_cursor into @idnum,@qnty
while @@fetch_status=0
begin
if @idNum<>@flagId
begin
select @bSum=case when (select isnull(sum(tempQnty),0) from @c where idnum=@idnum)>0
then (select sum(qnty) from @b where idnum=@idnum)-(select sum(tempQnty) from @c where idnum=@idnum)
else (select sum(Qnty) from @b where idnum=@idnum)
end
if @qnty-@bSum>=0 --已经减完
begin
insert into @c(idnum,qnty,tempqnty,lastqnty)
select @idnum,@qnty,@bSum,@qnty-@bSum
select @flagId=@idNum
end
else
begin
insert into @c(idnum,qnty,tempqnty,lastqnty)
select @idnum,@qnty,@qnty,0
select @flagId = 0
end
end
fetch next from my_cursor into @idnum,@qnty
end
close my_cursor
deallocate my_cursor
select * from @c
/*
结果:
idnum qnty tempqnty lastqnty
---------------------------------------------
1 10 10 0
1 15 1 14
2 50 50 0*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货