create table t1 (a int identity(1,1),b int) insert t1(b) select 30 union all select 50 union all select 60 union all select 20 union all select 40select * into #aa from( select *,d=(select sum(b) from t1 bb where bb.a<=aa.a) from t1 aa )cc update #aa set d=case when a<(select top 1 a from #aa where d>=100) then b when a=(select top 1 a from #aa where d>=100) then 100-(select d from #aa where a=(select top 1 a from #aa where d<100 order by d desc)) else 0 endselect * from #aa drop table #aa drop table t1
--测试 create table t1 (a int identity(1,1),b int) insert t1(b) select 30 union all select 50 union all select 60 union all select 20 union all select 40select * into #aa from( select *,d=(select sum(b) from t1 bb where bb.a<=aa.a) from t1 aa )cc update #aa set d=case when a<(select top 1 a from #aa where d>=100) then b when a=(select top 1 a from #aa where d>=100) then 100-(select d from #aa where a=(select top 1 a from #aa where d<100 order by d desc)) else 0 endselect * from #aa --drop table #aa --drop table t1a b d -------------------- 1 30 30 2 50 50 3 60 20 4 20 0 5 40 0
有时在解决某个问题时如果值不值得,得换一个思路了。
嘻嘻~~~这是竹之草说的
insert t1(b)
select 30
union all select 50
union all select 60
union all select 20
union all select 40select * into #aa from(
select *,d=(select sum(b) from t1 bb where bb.a<=aa.a) from t1 aa
)cc
update #aa
set d=case
when a<(select top 1 a from #aa where d>=100)
then b
when a=(select top 1 a from #aa where d>=100)
then 100-(select d from #aa where a=(select top 1 a from #aa where d<100 order by d desc))
else 0
endselect * from #aa
drop table #aa
drop table t1
create table t1 (a int identity(1,1),b int)
insert t1(b)
select 30
union all select 50
union all select 60
union all select 20
union all select 40select * into #aa from(
select *,d=(select sum(b) from t1 bb where bb.a<=aa.a) from t1 aa
)cc
update #aa
set d=case
when a<(select top 1 a from #aa where d>=100)
then b
when a=(select top 1 a from #aa where d>=100)
then 100-(select d from #aa where a=(select top 1 a from #aa where d<100 order by d desc))
else 0
endselect * from #aa
--drop table #aa
--drop table t1a b d
--------------------
1 30 30
2 50 50
3 60 20
4 20 0
5 40 0