其实这也不是一个算法，自己总结了下把它作为一个算法。
在很多地方很广泛的应用。多应用于逐条比较，自上而下或是自下而上。
这种可以拥游标实现但是却非常的慢。
个人总结了几种情况的应用附带例子说明：
declare @a table (a int,b int,s int)
declare @b table (a int,b int,c int,d int,m int)
insert @a
select 1,2,20
union all
select 2,3,15
insert @b
select 1,2,3,9,5
union all
select 1,2,4,10,12
union all
select 1,2,5,11,10
union all
select 1,2,6,12,5
union all
select 2,3,4,6,10
union all
select 2,3,5,7,5
union all
select 2,3,6,8,10
@a表
a b s
----------- ----------- -----------
1 2 20
2 3 15
@b表
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 3 9 5
1 2 4 10 12
1 2 5 11 10
1 2 6 12 5
2 3 4 6 10
2 3 5 7 5
2 3 6 8 10
现在做以下说明：具体要求如下：
参照@a表中的字段 a，b
去@b表中找相同的数据例：
@a表中
a b s
----------- ----------- -----------
1 2 20
在@b表中相对对应的数据有
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 3 9 5
1 2 4 10 12
1 2 5 11 10
1 2 6 12 5
然后再以@b标为基准，按照d从小到大，用@a中的s=20逐条和@b表中的m进行比较
第一条 20>5 该数据不取出
第二条 15>12该数据不取出
第三条 3<10该数据取出,m被更新成7
第四条 数据直接取出
结果为：
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 5 11 7
1 2 6 12 5
例子如上；；；；；；；；；；；；；；
整个过程执行完最后的结果为：
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 5 11 7
1 2 6 12 5
2 3 6 8 10
解决方法一：
select * from (
select n1.a,n1.b,n1.c,n1.d
,(case when
((select
sum(s)
from @a
where a=n1.a and b=n1.b
and a=n2.a and b=n2.b
)-
isnull((select
sum(m) from @b
where a=n1.a and b=n1.b
and a=n2.a and b=n2.b
and c<=n1.c
and d<=n1.d
),0))>=0
then 0
else
case when n1.m +((select
sum(s) from @a
where a=n1.a and b=n1.b
and a=n2.a and b=n2.b
) -
isnull((select
sum(m) from @b
where a=n1.a and b=n1.b
and a=n2.a and b=n2.b
and c<=n1.c
and d<=n1.d
),0))>=0
then -((select
sum(s) from @a
where a=n1.a and b=n1.b
and a=n2.a and b=n2.b
) -
isnull((select
sum(m) from @b
where a=n1.a and b=n1.b
and a=n2.a and b=n2.b
and c<=n1.c
and d<=n1.d
),0))
else n1.m
end
end )mm
from @b n1 ,@a n2
where
n1.a=n2.a
and n1.b=n2.b
)bb
where bb.mm>0
解决方法二：
select b.a,b.b,b.c,b.d,
case when b.summ-b.m>a.s then b.m else b.summ-a.s end as m
from @a a,(
select x.*,(select sum(m) from @b where a=x.a and b=x.b and d<=x.d) as Summ
from @b x
) b
where a.a=b.a
and a.b=b.b
and b.summ>a.s
例二：
declare @a table (a int,b int,s int)
declare @b table (a int,b int,c int,d int,m int)
insert @a
select 1,2,20
union all
select 2,3,15
insert @b
select 1,2,3,9,5
union all
select 1,2,4,10,12
union all
select 1,2,5,11,10
union all
select 1,2,6,12,5
union all
select 2,3,4,6,10
union all
select 2,3,5,7,5
union all
select 2,3,6,8,10
@a表
a b s
----------- ----------- -----------
1 2 20
2 3 15
@b表
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 3 9 5
1 2 4 10 12
1 2 5 11 10
1 2 6 12 5
2 3 4 6 10
2 3 5 7 5
2 3 6 8 10
给大家解释下：
上面有两个表@a,@b，大家都能看出来共同的字段是 a,b
但是下面的@b表中字段还有c,d
操作过程为
以a,b为连接条件将两表连接，明显看出在@b表中的数据会有多条
然后以@b标为基准把两表中a,b字段相同的数据依次按照d字段降序从大到小，逐条进行比较
最后小于0的去掉。
举例 @a表 中
a b s
----------- ----------- -----------
1 2 20
表@b中 与其相关的数据为
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 3 9 5
1 2 4 10 12
1 2 5 11 10
1 2 6 12 5
按照d降序@b中数据为
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 3 12 5
1 2 4 11 10
1 2 5 10 12
1 2 6 9 5
组条比较用@a中的s和@b中的m比较
则第一条 20>5第一条保留
比较第二条 15>10 第二条保留
比较第三条 5 <12 第三条仍然把保留 但是m数量标为5
第四条 就直接不用要了
结果就是下面
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 3 12 5
1 2 4 11 10
1 2 5 10 5
最终结果：
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 4 10 5
1 2 5 11 10
1 2 6 12 5
2 3 5 7 5
2 3 6 8 10
解决方法一：
select b.a,b.b,b.c,b.d,
case when b.summ>a.s then a.s-b.summ+b.m else b.m end as m
from @a a,(
select x.*,(select sum(m) from @b where a=x.a and b=x.b and d>=x.d) as Summ
from @b x
) b
where a.a=b.a
and a.b=b.b
and b.summ-b.m<a.s
order by b.a,b.b,b.d desc
解决方法二：
select * into # from
(select b.a,b.b,c,d,m=case when (select sum(m) from @b where a=b.a and d<=b.d)>a.s
then (select sum(m) from @b where a=b.a and d<=b.d)-a.s else 0 end from @b b,@a a
where a.a=b.a)a where m!=0
select a,b,c,d,m=a.m-(select isnull(sum(m),0) from # where a=a.a and d<a.d) from # a
解决方法三：
Select
M.a,M.b,M.c,M.d,
m=Case When ISNULL(N.s-(Select SUM(m) From @b Where a=M.a AND b=M.b AND d>M.d),M.m)>=M.m
Then M.m Else ISNULL(N.s-(Select SUM(m) From @b Where a=M.a AND b=M.b AND d>M.d),M.m)End
From @a N Left Join @b M
On N.a =M.a And N.b=m.b
Where ISNULL(N.s-(Select SUM(m) From @b Where a=M.a AND b=M.b AND d>M.d),M.m)>0
Order By m.d Desc
例三：
declare @a table (a int,b int,d int,s int)
declare @b table (a int,b int,c int,d int,m int)
insert @a
select 1,2,10,-20
union all
select 2,3,7,-15
union all
select 3,8,6,-50
insert @b
select 1,2,3,9,5
union all
select 1,2,4,10,12
union all
select 1,2,5,11,10
union all
select 1,2,6,12,5
union all
select 2,3,4,6,10
union all
select 2,3,5,7,5
union all
select 2,3,6,8,10
@a表
a b d s
----------- ----------- ----------- -----------
1 2 10 -20
2 3 7 -15
3 8 6 -50
@b表
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 3 9 5
1 2 4 10 12
1 2 5 11 10
1 2 6 12 5
2 3 4 6 10
2 3 5 7 5
2 3 6 8 10
现做以下说明：
还是和上面的一样，开始参照@a表中的 a,b两个字段
在@b表中找到与其相关的列
不同的是@a表中加上了一列d
那么在@b表中找相关列时就要找在@b表中d字段>=@a表中的d字段的记录。
然后逐条进行比较
举例：
@a表
a b d s
----------- ----------- ----------- -----------
1 2 10 -20
@b表
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 3 9 5
1 2 4 10 12
1 2 5 11 10
1 2 6 12 5
先找符合条件看的列 @a表中d=10a那么
@b表中符合条件的列为
@b表
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 3 9 5
1 2 4 10 12
先用@a表中的s列=-20与@b表中的m列逐条进行比较。
结果为：
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 4 10 -3
1 2 5 11 10
1 2 6 12 5
-----------------------------------------------------------------------------------------------------------------------
但是如果把
@a表中改为
a b d s
----------- ----------- ----------- -----------
1 2 11 -20
结果将变为
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 5 11 7
1 2 6 12 5
特别注意一种情况：
像@a表中在@b表中没有相关项的情况
应该插入到b表中。
例
@a表
a b d s
----------- ----------- ----------- -----------
3 8 6 -50
结果为
a b c d m
----------- ----------- ----------- ----------- -----------
3 8 6 -50
------------------------------------------------------------------------------------------------------------------
最终计算结果如下
@a表
a b d s
----------- ----------- ----------- -----------
1 2 10 -20
2 3 7 -15
3 8 6 -50
@b表
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 3 9 5
1 2 4 10 12
1 2 5 11 10
1 2 6 12 5
2 3 4 6 10
2 3 5 7 5
2 3 6 8 10
||
||
结果为
a b c d m
----------- ----------- ----------- ----------- -----------
1 2 4 10 -3
1 2 5 11 10
1 2 6 12 5
2 3 6 8 10
3 8 6 -50
解答一：
select b.a,b.b,b.c,b.d,
case when b.summ+a.s>b.m then b.m else b.summ+a.s end as m
into #temp
from @a a,(
select x.*,(select sum(m) from @b where a=x.a and b=x.b and d<=x.d) as Summ
from @b x
) b
where a.a=b.a
and a.b=b.b
--and b.summ>a.s
and b.d<=a.d
select * from #temp a
where not exists(select 1 from #temp where a=a.a and b=a.b and m>a.m)
and a.m<>0
union all
select n.* from @b n,@a m
where
n.a=m.a
and
n.b=m.b
and
n.d>m.d
union all
select
m.a
,m.b
,null
,m.d
,m.s
from
@a m
where
not exists(select 1 from #temp where a=m.a and b=m.b)
order by a
,d
asc
drop table #temp
