有两个表A,B 相同字段 ,编码 CHAR(5),库房数量 int,库房 char(9)
有几种情况:
A有重复编码的时候,B也有可能重复,只想用A的相同编码的库房数量-B的相同编码数量,然后更新A减数的那一行
A表
001 10 A
001 20 AB表
001 10 C当这种情况如何更新
A表
001 10 A
001 20 AB表
001 10 C
001 10 B
有几种情况:
A有重复编码的时候,B也有可能重复,只想用A的相同编码的库房数量-B的相同编码数量,然后更新A减数的那一行
A表
001 10 A
001 20 AB表
001 10 C当这种情况如何更新
A表
001 10 A
001 20 AB表
001 10 C
001 10 B
select top 1 f3
from b
where f1=a.f1 and f2=a.f2
order by f3这样得到的f3标识了b表该减的那一行
有两个表A,B 相同字段 ,编码 CHAR(5),库房数量 int,库房 char(9)
有几种情况:
A有重复编码的时候,B也有可能重复,只想用A的相同编码的库房数量-B的相同编码数量,然后更新A减数的那一行
A表
001 10 A
001 20 AB表
001 10 C得到A结果
001 0 A
001 20 A
当这种情况如何更新
A表
001 10 A
001 20 AB表
001 10 C
001 10 B
得到A结果
A表
001 0 A
001 10 A
B的不变,就想把A的记录更新为减完B的数量
if object_id('tempdb..#A') is not null drop table #A
select * into #A from (
select '001' as CD, 10 as QTY, 'A' as WHS
union all
select '001', 20, 'A') aif object_id('tempdb..#B') is not null drop table #B
select * into #B from (
select '001' as CD, 10 as QTY, 'B' as WHS
union all
select '001', 10, 'C') aalter table #A add nsh int IDENTITY (1, 1)
alter table #B add nsh int IDENTITY (1, 1), OUT numeric(18,0)
GOupdate #B set OUT=QTYdeclare @cA cursor
declare @CD varchar(30)
declare @QTY numeric(18,6)
declare @nsh int
set @cA=cursor for select CD, QTY, nsh from #A
open @cA
fetch next from @cA into @CD, @QTY, @nsh
while @@fetch_status<>-1
begin
declare @currMatch numeric(18,0) set @currMatch=@QTY
update #B set OUT=OUT-(case when OUT>@QTY then @QTY else OUT end), @currMatch=(case when OUT>@QTY then @QTY else OUT end) where nsh in(
select top 1 nsh from #B where CD=@CD and OUT>0 order by CD, OUT, nsh
)
if @@ROWCOUNT>0 update #A set QTY=@currMatch where nsh=@nsh fetch next from @cA into @CD, @QTY, @nsh
endselect * from #A
select * from #B
if @@ROWCOUNT>0 update #A set QTY=QTY-@currMatch where nsh=@nsh这样就可以了:
--建表A
if object_id('tempdb..#A') is not null drop table #A
select * into #A from (
select '001' as CD, 10 as QTY, 'A' as WHS
union all
select '001', 20, 'A') a
--建表B
if object_id('tempdb..#B') is not null drop table #B
select * into #B from (
select '001' as CD, 10 as QTY, 'B' as WHS
union all
select '001', 10, 'C') a
--分别加字段:表A的自动编号,表B的outstanding数量
alter table #A add nsh int IDENTITY (1, 1)
alter table #B add nsh int IDENTITY (1, 1), OUT numeric(18,0)
GO
--初始化未完成量
update #B set OUT=QTY--(上午看到一个贴人家说不要用游标,我不知那人是怎么实现此类问题的)
declare @cA cursor
declare @CD varchar(30)
declare @QTY numeric(18,6)
declare @nsh int
set @cA=cursor for select CD, QTY, nsh from #A
open @cA
fetch next from @cA into @CD, @QTY, @nsh
while @@fetch_status<>-1
begin
--(这段虽解决了楼主的例子,但对复杂的情况还是不够,比如要扣几次才能扣完的,需要在此再嵌个循环)
declare @currMatch numeric(18,0) set @currMatch=@QTY
update #B
set OUT=OUT-(case when OUT>@QTY then @QTY else OUT end),
@currMatch=(case when OUT>@QTY then @QTY else OUT end)
where nsh in(
select top 1 nsh from #B where CD=@CD and OUT>0 order by CD, OUT, nsh
)
if @@ROWCOUNT>0 update #A set QTY=QTY-@currMatch where nsh=@nsh --如果有匹配的就更新 fetch next from @cA into @CD, @QTY, @nsh
end--这就是结果了
select * from #A
select * from #B