--楼主的就可以用下面的存储过程 create proc p_process as declare @t table(cname varchar(2),sl int,ph varchar(10),sl1 int,sl2 int) insert into @t(cname,ph,sl1,sl2) select a.cname,a.ph,a.sl,b.sl from @t1 a join @t2 b on a.cname=b.cname order by a.cnamedeclare @cname varchar(2),@sl int,@s2 int update @t set @sl=case cname when @cname then @s2 else sl2 end ,sl=case when @sl>sl1 then sl1 else @sl end ,@s2=@sl-sl1,@cname=cnameselect cname,sl,ph from @t go
--下面是数据测试--测试数据 declare @t1 table(cname varchar(2),sl int,ph varchar(10)) insert into @t1 select 'a',100,'010101' union all select 'a',200,'020304' union all select 'b',300,'040506'declare @t2 table(cname varchar(2),sl int,ph varchar(10)) insert into @t2 select 'a',150,'010101' union all select 'b',200,'040506' --数据处理 declare @t table(cname varchar(2),sl int,ph varchar(10),sl1 int,sl2 int) insert into @t(cname,ph,sl1,sl2) select a.cname,a.ph,a.sl,b.sl from @t1 a join @t2 b on a.cname=b.cname order by a.cnamedeclare @cname varchar(2),@sl int,@s2 int update @t set @sl=case cname when @cname then @s2 else sl2 end ,sl=case when @sl>sl1 then sl1 else @sl end ,@s2=@sl-sl1,@cname=cname--显示处理结果 select cname,sl,ph from @t /*--处理结果 cname sl ph ----- ----------- ---------- a 100 010101 a 50 020304 b 200 040506(所影响的行数为 3 行)--*/
create table #目标库(cname varchar(10),sl int,ph varchar(10)) insert #目标库 values('a' , 100 ,'010101') insert #目标库 values('a' , 200 ,'020304') insert #目标库 values('a' , 200 ,'020305') insert #目标库 values('a' , 200 ,'020306') insert #目标库 values('b', 300 ,'040506') insert #目标库 values('b', 300 ,'040507')create table #临时表(cname varchar(10),sl int,ph varchar(10)) insert #临时表 values('a', 150 ,'010101') insert #临时表 values('a', 1050 ,'020305') insert #临时表 values('b', 200 ,'040506')select a.*,b.sl value,0 temp into #a from #目标库 a left join #临时表 b on a.ph=b.ph order by a.cname,a.phdeclare @a int,@a2 int,@b varchar(10) select @b='',@a=0,@a2=0 update #a set @a2=case when cname<>@b and isnull(value,0)>sl then sl when cname=@b and isnull(value,0)+@a>sl then sl when cname<>@b and isnull(value,0)<=sl then isnull(value,0) when cname=@b and @a+isnull(value,0)<=sl then @a+isnull(value,0) end, @a=case when cname<>@b and isnull(value,0)>=sl then isnull(value,0)-sl when cname=@b and isnull(value,0)+@a>=sl then isnull(value,0)+@a-sl else 0 end, @b=cname, temp=@a, value=@a2select * from #a go drop table #目标库,#临时表,#a
--可以应对许多情况。create table #目标库(cname varchar(10),sl int,ph varchar(10)) insert #目标库 values('a' , 100 ,'010101') insert #目标库 values('a' , 200 ,'020304') insert #目标库 values('a' , 200 ,'020305') insert #目标库 values('a' , 200 ,'020306') insert #目标库 values('b', 300 ,'040506') insert #目标库 values('b', 300 ,'040507')create table #临时表(cname varchar(10),sl int,ph varchar(10)) insert #临时表 values('a', 150 ,'010101') insert #临时表 values('a', 1050 ,'020305') insert #临时表 values('b', 200 ,'040506')select a.*,b.sl value into #a from #目标库 a left join #临时表 b on a.ph=b.ph order by a.cname,a.phdeclare @a int,@a2 int,@b varchar(10) select @b='',@a=0,@a2=0 update #a set @a2=case when cname<>@b and isnull(value,0)>sl then sl when cname=@b and isnull(value,0)+@a>sl then sl when cname<>@b and isnull(value,0)<=sl then isnull(value,0) when cname=@b and @a+isnull(value,0)<=sl then @a+isnull(value,0) end, @a=case when cname<>@b and isnull(value,0)>=sl then isnull(value,0)-sl when cname=@b and isnull(value,0)+@a>=sl then isnull(value,0)+@a-sl else 0 end, @b=cname, value=@a2select cname,value sl,ph from #a go drop table #目标库,#临时表,#a /* cname sl ph ---------- ----------- ---------- a 100 010101 a 50 020304 a 200 020305 a 200 020306 b 200 040506 b 0 040507(所影响的行数为 6 行) */
create proc p_process as select * into # from 目标库 order by cnameif not exists (select * from (select * from (select cname,sum(sl) sl, ph from 临时库 group by cname,ph) b left join (select cname,sum(sl) sl, ph from 目标库 group by cname,ph) a on a.cname =b.cname and a.ph=b.ph where isnull(a.sl,0)< isnull(b.sl) ) begin declare @cname varchar(2),@sl int,ph varchar(10),@sl1 int update @t set @sl= case a.cname=@cname and a.ph=@ph then @sl - @sl1 else zsl end , @cname=a.cname ,@ph=a.ph, @sl1=case when sl> @sl then @sl else sl end, sl=sl - @sl1 from @t a inner join (select cname,ph,sum(sl) zsl from 临时库 group by cname,ph) b on a.cname=b.cname and b.ph=c.ph select cname,sl,ph from @t end go
create proc p_process
as
declare @t table(cname varchar(2),sl int,ph varchar(10),sl1 int,sl2 int)
insert into @t(cname,ph,sl1,sl2)
select a.cname,a.ph,a.sl,b.sl
from @t1 a join @t2 b on a.cname=b.cname
order by a.cnamedeclare @cname varchar(2),@sl int,@s2 int
update @t set @sl=case cname when @cname then @s2 else sl2 end
,sl=case when @sl>sl1 then sl1 else @sl end
,@s2=@sl-sl1,@cname=cnameselect cname,sl,ph from @t
go
declare @t1 table(cname varchar(2),sl int,ph varchar(10))
insert into @t1
select 'a',100,'010101'
union all select 'a',200,'020304'
union all select 'b',300,'040506'declare @t2 table(cname varchar(2),sl int,ph varchar(10))
insert into @t2
select 'a',150,'010101'
union all select 'b',200,'040506'
--数据处理
declare @t table(cname varchar(2),sl int,ph varchar(10),sl1 int,sl2 int)
insert into @t(cname,ph,sl1,sl2)
select a.cname,a.ph,a.sl,b.sl
from @t1 a join @t2 b on a.cname=b.cname
order by a.cnamedeclare @cname varchar(2),@sl int,@s2 int
update @t set @sl=case cname when @cname then @s2 else sl2 end
,sl=case when @sl>sl1 then sl1 else @sl end
,@s2=@sl-sl1,@cname=cname--显示处理结果
select cname,sl,ph from @t
/*--处理结果
cname sl ph
----- ----------- ----------
a 100 010101
a 50 020304
b 200 040506(所影响的行数为 3 行)--*/
insert #目标库 values('a' , 100 ,'010101')
insert #目标库 values('a' , 200 ,'020304')
insert #目标库 values('a' , 200 ,'020305')
insert #目标库 values('a' , 200 ,'020306')
insert #目标库 values('b', 300 ,'040506')
insert #目标库 values('b', 300 ,'040507')create table #临时表(cname varchar(10),sl int,ph varchar(10))
insert #临时表 values('a', 150 ,'010101')
insert #临时表 values('a', 1050 ,'020305')
insert #临时表 values('b', 200 ,'040506')select a.*,b.sl value,0 temp into #a from #目标库 a left join #临时表 b on a.ph=b.ph order by a.cname,a.phdeclare @a int,@a2 int,@b varchar(10)
select @b='',@a=0,@a2=0
update #a set
@a2=case
when cname<>@b and isnull(value,0)>sl then sl
when cname=@b and isnull(value,0)+@a>sl then sl
when cname<>@b and isnull(value,0)<=sl then isnull(value,0)
when cname=@b and @a+isnull(value,0)<=sl then @a+isnull(value,0)
end,
@a=case
when cname<>@b and isnull(value,0)>=sl then isnull(value,0)-sl
when cname=@b and isnull(value,0)+@a>=sl then isnull(value,0)+@a-sl
else 0
end,
@b=cname,
temp=@a,
value=@a2select * from #a
go
drop table #目标库,#临时表,#a
insert #目标库 values('a' , 100 ,'010101')
insert #目标库 values('a' , 200 ,'020304')
insert #目标库 values('a' , 200 ,'020305')
insert #目标库 values('a' , 200 ,'020306')
insert #目标库 values('b', 300 ,'040506')
insert #目标库 values('b', 300 ,'040507')create table #临时表(cname varchar(10),sl int,ph varchar(10))
insert #临时表 values('a', 150 ,'010101')
insert #临时表 values('a', 1050 ,'020305')
insert #临时表 values('b', 200 ,'040506')select a.*,b.sl value into #a from #目标库 a left join #临时表 b on a.ph=b.ph order by a.cname,a.phdeclare @a int,@a2 int,@b varchar(10)
select @b='',@a=0,@a2=0
update #a set
@a2=case
when cname<>@b and isnull(value,0)>sl then sl
when cname=@b and isnull(value,0)+@a>sl then sl
when cname<>@b and isnull(value,0)<=sl then isnull(value,0)
when cname=@b and @a+isnull(value,0)<=sl then @a+isnull(value,0)
end,
@a=case
when cname<>@b and isnull(value,0)>=sl then isnull(value,0)-sl
when cname=@b and isnull(value,0)+@a>=sl then isnull(value,0)+@a-sl
else 0
end,
@b=cname,
value=@a2select cname,value sl,ph from #a
go
drop table #目标库,#临时表,#a
/*
cname sl ph
---------- ----------- ----------
a 100 010101
a 50 020304
a 200 020305
a 200 020306
b 200 040506
b 0 040507(所影响的行数为 6 行)
*/
as
select * into # from 目标库 order by cnameif not exists (select * from (select * from (select cname,sum(sl) sl, ph from 临时库 group by cname,ph) b left join (select cname,sum(sl) sl, ph from 目标库 group by cname,ph) a on a.cname =b.cname and a.ph=b.ph where isnull(a.sl,0)< isnull(b.sl) )
begin
declare @cname varchar(2),@sl int,ph varchar(10),@sl1 int
update @t set @sl=
case a.cname=@cname and a.ph=@ph then @sl - @sl1 else zsl end ,
@cname=a.cname ,@ph=a.ph,
@sl1=case when sl> @sl then @sl else sl end,
sl=sl - @sl1
from @t a inner join (select cname,ph,sum(sl) zsl from 临时库 group by cname,ph) b on a.cname=b.cname and b.ph=c.ph
select cname,sl,ph from @t
end
go