declare @a table (a int, b int,c int,m int)
declare @b table (a int,b int,c int,n int) insert @a
select 1,2,3,-12
union all
select 1,3,1,-8 insert @b
select 1,2,1,5
union
all
select 1,2,2,6
union all
select 1,2,3,10
union all
select 1,3,1,10 select * from @a
a b c m
----------- ----------- ----------- -----------
1 2 3 -12
1 3 1 -8
select * from @b a b c n
----------- ----------- ----------- -----------
1 2 1 5
1 2 2 6
1 2 3 10
1 3 1 10 得到以下的结果我自己写出来了:
a b c n
----------- ----------- ----------- -----------
1 2 1 -7
1 2 2 -1
1 2 3 9
1 3 1 2
算法如下:
update p
set
n=
case when
z.sumn+y.m>z.n
then
z.n
else
z.sumn+y.m
end
from @a y
,@b p
,(select
x.*
,(select sum(n)
from @b
where
a=x.a
and b=x.b
and c <=x.c)sumn
from @b x)z
where
z.a=y.a
and
z.b=y.b
and
z.c <=y.c
and
p.a=z.a
and p.b=z.b
and p.c=z.c 上面高手一般都能看懂。。就是列a,b为主要联结的相等条件,@b中的c一定要小于等于@a中的c
但是 我如果变成,
declare @a table (a int, b int,c int,m int)
declare @b table (a int,b int,c int,n int) insert @a
select 1,2,3,-12
union all
select 1,3,1,-8
union all
select 1,2,3,-3 insert @b
select 1,2,1,5
union
all
select 1,2,2,6
union all
select 1,2,3,10
union all
select 1,3,1,10 select * from @a
a b c m
----------- ----------- ----------- -----------
1 2 3 -12
1 2 3 -3
1 3 1 -8 select * from @b
a b c n
----------- ----------- ----------- -----------
1 2 1 5
1 2 2 6
1 2 3 10
1 3 1 10
想得到下面的结果:
就是在上面第一次那个记过上面从不是0的开始,进行比较算出来的。 a b c n
----------- ----------- ----------- -----------
1 2 1 -7
1 2 2 -1
1 2 3 6
1 3 1 2
declare @b table (a int,b int,c int,n int) insert @a
select 1,2,3,-12
union all
select 1,3,1,-8 insert @b
select 1,2,1,5
union
all
select 1,2,2,6
union all
select 1,2,3,10
union all
select 1,3,1,10 select * from @a
a b c m
----------- ----------- ----------- -----------
1 2 3 -12
1 3 1 -8
select * from @b a b c n
----------- ----------- ----------- -----------
1 2 1 5
1 2 2 6
1 2 3 10
1 3 1 10 得到以下的结果我自己写出来了:
a b c n
----------- ----------- ----------- -----------
1 2 1 -7
1 2 2 -1
1 2 3 9
1 3 1 2
算法如下:
update p
set
n=
case when
z.sumn+y.m>z.n
then
z.n
else
z.sumn+y.m
end
from @a y
,@b p
,(select
x.*
,(select sum(n)
from @b
where
a=x.a
and b=x.b
and c <=x.c)sumn
from @b x)z
where
z.a=y.a
and
z.b=y.b
and
z.c <=y.c
and
p.a=z.a
and p.b=z.b
and p.c=z.c 上面高手一般都能看懂。。就是列a,b为主要联结的相等条件,@b中的c一定要小于等于@a中的c
但是 我如果变成,
declare @a table (a int, b int,c int,m int)
declare @b table (a int,b int,c int,n int) insert @a
select 1,2,3,-12
union all
select 1,3,1,-8
union all
select 1,2,3,-3 insert @b
select 1,2,1,5
union
all
select 1,2,2,6
union all
select 1,2,3,10
union all
select 1,3,1,10 select * from @a
a b c m
----------- ----------- ----------- -----------
1 2 3 -12
1 2 3 -3
1 3 1 -8 select * from @b
a b c n
----------- ----------- ----------- -----------
1 2 1 5
1 2 2 6
1 2 3 10
1 3 1 10
想得到下面的结果:
就是在上面第一次那个记过上面从不是0的开始,进行比较算出来的。 a b c n
----------- ----------- ----------- -----------
1 2 1 -7
1 2 2 -1
1 2 3 6
1 3 1 2
就是,如果A表有4条数据呢,结果如何?
a b c m
----------- ----------- ----------- -----------
1 2 3 -12
1 2 3 -3
1 2 3 -5
1 3 1 -8
declare @b table (a int,b int,c int,n int) insert @a
select 1,2,3,-12
union all
select 1,3,1,-8
union all
select 1,2,3,-3 insert @b
select 1,2,1,5
union
all
select 1,2,2,6
union all
select 1,2,3,10
union all
select 1,3,1,10 --用个临时表把@a表a,b,c字段重复记录编号
declare @a1 table (a int, b int,c int,m int,id int,Summ int)
insert @a1 (a,b,c,m)
select a,b,c,m from @a order by a,b,c
declare @na int
declare @nb int
declare @nc int
declare @id int
declare @Summ intupdate @a1 set
@Summ=case when a=@na and b=@nb and c=@nc then @Summ+m else m end,
@id=case when a=@na and b=@nb and c=@nc then @id+1 else 1 end,
@na=a,
@nb=b,
@nc=c,
Summ=@Summ,
id=@id
--修改更新条件
update p
set
n=
case when
z.sumn+y.summ>z.n
then
z.n
else
z.sumn+y.summ
end
from @a1 y
,@b p
,(select
x.*
,(select sum(n)
from @b
where
a=x.a
and b=x.b
and c <=x.c)sumn
from @b x)z
where
z.a=y.a
and
z.b=y.b
and
z.c <=y.c
and
(z.sumn+y.summ<=0
or not exists (
select 1 from @a1
where a=y.a
and b=y.b
and c=y.c
and id>y.id
)
)
and p.a=z.a
and p.b=z.b
and p.c=z.c --显示结果
select * from @b--结果
a b c n
----------- ----------- ----------- -----------
1 2 1 -7
1 2 2 -1
1 2 3 6
1 3 1 2(4 行受影响)
declare @b table (a int,b int,c int,n int) insert @a
select 1,2,3,-12
union all
select 1,3,1,-8
union all
select 1,2,3,-3 insert @b
select 1,2,1,5
union
all
select 1,2,2,6
union all
select 1,2,3,10
union all
select 1,3,1,10 select *,identity(int,1,1) id into #fc_mm from @a
select *,identity(int,1,1) id into #fc_gg from @bselect *,sn=(select sum(n) sn from #fc_gg where a=a.a and b=a.b and id<=a.id) into #fc1 from #fc_gg a
select *,sm=(select sum(m) sm from #fc_mm where a=a.a and b=a.b and id<=a.id) into #fc2 from #fc_mm a
select a.a,a.b,a.c,sm+sn s,id=identity(int) into #fc from #fc1 a
left join #fc2 b
on a.a=b.a and a.b=b.b
and (
abs(sm)>=sn
and not exists(
select 1 from #fc2 where a=b.a and b=b.b and id<b.id)
or
abs(sm)<sn
and abs(sm)>(isnull((select top 1 sn from #fc1 where a=a.a and b=a.b and id<a.id order by id desc),0))
)
--select * from #fc
/*
1 2 1 -7 1
1 2 2 -1 2
1 2 3 9 3 -- 这里多出一条记录,因为在取的时候第三条记录发生过过渡,按楼主的要求,不需要这条记录,只保留其对应最终结果
1 2 3 6 4
1 3 1 2 5
*/
select a,b,c,s from #fc a
where not exists(select 1 from #fc where a=a.a and b=a.b and c=a.c and id>a.id)/*
最终结果
1 2 1 -7
1 2 2 -1
1 2 3 6
1 3 1 2
*/drop table #fc_mm,#fc_gg,#fc1,#fc2,#fc
----------- ----------- ----------- -----------
1 2 1 -7
1 2 2 -1
1 2 3 1
1 3 1 2(4 行受影响)
参见查询题解例题 :4,解题举例
a. 数据是这样的
表1
发票号码 金额
A-1 1000.00
A-2 1500.00
A-3 1200.00
B-1 800.00
B-2 1000.00
B-3 900.00
B-4 1100.00
...表2
发票号码 产品类型 金额
A P1 2000.00
A P2 1700.00
B P1 1500.00
B P2 1200.00
B P3 1100.00
...希望产生下面的表:
发票号码 产品类型 金额
A-1 P1 1000.00
A-2 P1 1000.00
A-2 P2 500.00
A-3 P2 1200.00
B-1 P1 800.00
B-2 P1 700.00
B-2 P2 300.00
B-3 P2 900.00
B-4 P3 1100.00
你的是:
a b c n
----------- ----------- ----------- -----------
1 2 1 -10
1 2 2 -4
1 2 3 6
1 3 1 2
当然我的又加了些操作, 得到了最终想要的结果
declare @a table (a int, b int,c int,m int)
declare @b table (a int,b int,c int,n int)insert @a
select 1,2,4,-22
union all
select 1,3,1,-8
union all
select 1,2,5,-9
declare @i int
declare @j int
set @j=1select id=IDENTITY(int,1,1),*
into #
from @aselect @i=max(id) from #insert @b
select 1,2,1,5
union
all
select 1,2,2,6
union all
select 1,2,3,10
union all
select 1,3,1,10select * from @aselect * from @b
select * from #
while (@j<=@i)
begin
update p
set
p.c=case when (case when
z.sumn+y.m>z.n
then
z.n
else
z.sumn+y.m
end)<0
then y.c
else
p.c
end
,n=
case when
z.sumn+y.m>z.n
then
z.n
else
z.sumn+y.m
endfrom
@b p
,(select * from # where id=@j ) y
,
(select
x.*
,(select sum(n)
from @b
where
a=x.a
and b=x.b
and c<=x.c
and n>0)sumn
from @b x
where x.n>0)z
where
z.a=y.a
and
z.b=y.b
and
z.c<=y.c
and p.a=z.a
and p.b=z.b
and p.c=z.c
and p.n>0delete mm
from @b mm
where n<=0
and
exists (select 1 from @b where a=mm.a and b=mm.b and n>mm.n)set @j=@j+1
endselect * from @bdrop table #