use tempdb
go
set nocount on
go
if object_id (N'mid', N'U') is not null
begin
drop table mid
create table mid
(
bs varchar(1) not null,
total decimal(19, 2) not null,
)
end
go
if object_id (N'goal', N'U') is not null
begin
drop table goal
create table goal
(
xh int not null,
bs varchar(1) not null,
num decimal(19, 2) not null,
)
end
go
insert mid (bs, total) values ('a', 3000)
insert mid (bs, total) values ('b', 9000)
insert mid (bs, total) values ('c', 3000)
go
insert goal (xh, bs, num) values (1, 'a', 1000)
insert goal (xh, bs, num) values (2, 'a', 1000)
insert goal (xh, bs, num) values (3, 'a', 1000)
insert goal (xh, bs, num) values (6, 'b', 3000)
insert goal (xh, bs, num) values (7, 'b', 3000)
insert goal (xh, bs, num) values (8, 'b', 3000)
insert goal (xh, bs, num) values (11, 'b', 3000)
insert goal (xh, bs, num) values (16, 'c', 2000)
insert goal (xh, bs, num) values (18, 'c', 2000)
insert goal (xh, bs, num) values (19, 'd', 500)
go
select * from mid
select * from goal
-- 前提: mid表每个bs有个总量total, goal表每个bs有对应的分量, 而且有对应的非连续递增的xh
-- 问题: goal表如何按xh递减的顺序, 用mid表的total冲减其分量(mid表的总量<=goal表分量的和)(就是用mid表的total,去抵消goal表的num, 直到把total抵消成0, 按照goal表xh递减的顺序, 从大到小抵消...)
-- 最终结果:
-- goal表的数据
-- xh bs num
-- 1 a 0 -- 3.经过1和2.a初始total=1000,原num=1000=total=1000,所以total-1000=0,num=0
-- 2 a 0 -- 2.经过1,a初始total=2000,原num=1000<total=2000,所以total-1000=1000,num=0
-- 3 a 0 -- 1.a初始total=3000,原num=1000<total=3000,所以total-1000=2000,然后num=0
-- 6 b 3000 -- 此时total=0, num=3000>total=0,所以不改
-- 7 b 0 -- 同a的说明
-- 8 b 0 -- 同a的说明
-- 11 b 0 -- 同a的说明
-- 16 c 1000 -- 原num=2000>total=1000,所以total将原来的2000冲为0即可,最终total=0,num=1000
-- 18 c 0 -- 同a
-- 19 d 500 -- mid表没有d,不改
------解决方案--------------------
- SQL code
declare mycursor cursorforselect xh,bs,num from goal order by xh descopen mycursordeclare @xh int,@bs char(1),@num intfetch next from mycursor into @xh,@bs,@numwhile @@fetch_status=0begin declare @total int,@val int select @total=total from mid where [email protected] if @total is not null begin if @total>[email protected] set @[email protected] else set @[email protected] update goal set [email protected] where [email protected] update mid set [email protected] where [email protected] end fetch next from mycursor into @xh,@bs,@numendclose mycursordeallocate mycursorselect * from midselect * from goal-------------------------------bs total---- ---------------------------------------a 0.00b 0.00c 0.00(3 行受影响)xh bs num----------- ---- ---------------------------------------1 a 0.002 a 0.003 a 0.006 b 3000.007 b 0.008 b 0.0011 b 0.0016 c 1000.0018 c 0.0019 d 500.00(10 行受影响)
------解决方案--------------------
- SQL code
with c as(select a.rn,a.xh,a.bs,a.num,b.total from (select row_number() over(partition by bs order by xh desc) rn, xh,bs,num from goal) a left join mid b on a.bs=b.bs),d as(select c.xh,c.bs,case when c.total is null then c.numwhen (c.total-(select isnull(sum(c2.num),0) from c c2 where c2.bs=c.bs and c2.rn<=c.rn))>=0then 0 else abs(c.total-(select isnull(sum(c2.num),0) from c c2 where c2.bs=c.bs and c2.rn<=c.rn)) end numfrom c)update eset e.num=d.numfrom goal einner join d on e.xh=d.xh and e.bs=d.bs select * from goalxh bs num----------- ---- ----------------1 a 0.002 a 0.003 a 0.006 b 3000.007 b 0.008 b 0.0011 b 0.0016 c 1000.0018 c 0.0019 d 500.00