create table T1
(
ID int,NUM decimal(12,2)
)
create table T2
(
ID int,M_ID int,N0 int,N1 decimal(12,2)
)insert T1
select 1001, 800.88 union
select 1002, 2000.23 union
select 1003, 4000
insert T2
select 1, 1001, 300, null union
select 2, 1001, 333, null union
select 3, 1001, 444, null union
select 4, 1002, 300, null union
select 5, 1002, 403, null union
select 6, 1002, 444.44, null--更新
update T2
set N1=( case when T1.NUM>=(select sum(N0) from T2 where M_ID=T.M_ID and ID<=T.ID)
then T.N0
else ( case when T1.NUM>=(select sum(N0) from T2 where M_ID=T.M_ID and ID<=T.ID-1)
then T1.NUM-(select sum(N0) from T2 where M_ID=T.M_ID and ID<=T.ID-1)
else 0
end
)
end
)
from T2 T
join T1 on T.M_ID=T1.ID--查看
select * from T2--删除测试环境
drop table T1,T2--结果
/*
ID M_ID N0 N1
----------- ----------- ----------- --------------
1 1001 300 300.00
2 1001 333 333.00
3 1001 444 167.88
4 1002 300 300.00
5 1002 403 403.00
6 1002 444 444.00(所影响的行数为 6 行)
*/
(
ID int,NUM decimal(12,2)
)
create table T2
(
ID int,M_ID int,N0 int,N1 decimal(12,2)
)insert T1
select 1001, 800.88 union
select 1002, 2000.23 union
select 1003, 4000
insert T2
select 1, 1001, 300, null union
select 2, 1001, 333, null union
select 3, 1001, 444, null union
select 4, 1002, 300, null union
select 5, 1002, 403, null union
select 6, 1002, 444.44, null--更新
update T2
set N1=( case when T1.NUM>=(select sum(N0) from T2 where M_ID=T.M_ID and ID<=T.ID)
then T.N0
else ( case when T1.NUM>=(select sum(N0) from T2 where M_ID=T.M_ID and ID<=T.ID-1)
then T1.NUM-(select sum(N0) from T2 where M_ID=T.M_ID and ID<=T.ID-1)
else 0
end
)
end
)
from T2 T
join T1 on T.M_ID=T1.ID--查看
select * from T2--删除测试环境
drop table T1,T2--结果
/*
ID M_ID N0 N1
----------- ----------- ----------- --------------
1 1001 300 300.00
2 1001 333 333.00
3 1001 444 167.88
4 1002 300 300.00
5 1002 403 403.00
6 1002 444 444.00(所影响的行数为 6 行)
*/
最后一行应该是853.23,分不完放在最后一条记录
结贴