--下面是数据测试--测试数据
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 行)--*/
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 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
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 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', 150 ,'010101')
insert 临时表 values('a', 1050 ,'020305')
insert 临时表 values('b', 200 ,'040506')-------------------------------------------------------
select a.*,b.sl value into #a from 目标库 a left join (select cname,sum(sl) sl,ph from 临时表 group by cname,ph) 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
首先非常感谢 邹键 和大力 还有monkey,马可邹键的方法:如果如下的情况就不和题的意思了declare @t1 table(cname varchar(2),sl int,ph varchar(10))
insert into @t1
select 'a',200,'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 150 010101
b 200 040506
insert into @t1
select 'a',200,'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 where sl>0