declare @t table(ID int,amount int)
insert into @t select 1,40000
insert into @t select 2,70000
insert into @t select 3,80000
insert into @t select 4,90000declare @A int
declare @b decimal(18,2)
set @a = 40000
set @b=0.3select @a,@b--select id,(select sum(amount) * @b - @a from @t where id <= a.id) sum_temp from @t a
select id, ((select sum(amount) from @t where id <= a.id) - @a/@b) as dd into #temp from @t a
select top 1 * from #temp where dd > 0 order by dd
drop table #temp/*
id dd
----------- ----------------------------------
3 56666.6666666666666666667(所影响的行数为 1 行)
*/
insert into @t select 1,40000
insert into @t select 2,70000
insert into @t select 3,80000
insert into @t select 4,90000declare @A int
declare @b decimal(18,2)
set @a = 40000
set @b=0.3select @a,@b--select id,(select sum(amount) * @b - @a from @t where id <= a.id) sum_temp from @t a
select id, ((select sum(amount) from @t where id <= a.id) - @a/@b) as dd into #temp from @t a
select top 1 * from #temp where dd > 0 order by dd
drop table #temp/*
id dd
----------- ----------------------------------
3 56666.6666666666666666667(所影响的行数为 1 行)
*/
set @a=40000
set @b=0.3declare @t table(id int,amount int)
insert into @t select 1,40000
insert into @t select 2,70000
insert into @t select 3,80000
insert into @t select 4,90000select
@id=min(id)
from
@t a
where
(select sum(amount) from @t where id<=a.id)*@b>=@aselect ((select sum(amount) from @t where id<=@id)-@a/@b + amount) from @t where id=@id+1
set @A=20000
set @b=0.2select id,amount,case when col1>@A then col2-@A/@b else col1 end as col3 from (
select id,amount,(select sum(amount*@b)from T1 a where a.id<=T1.id) as col1,
(select sum(amount)from T1 a where a.id<=T1.id) as col2
from T1)a
where case when col1>@A then col2-@A/@b else col1 end=90000
drop table A
create table A
(
id int,
amount bigint
)
insert A
select 1,40000 union
select 2,70000 union
select 3,80000 union
select 4,90000select T.*,
(select sum(amount) from A where id<=T.id)*0.3,
case when (select sum(amount) from A where id<=T.id)*0.3>40000 then (select sum(amount) from A where id<=T.id)-40000/0.3 end
from A Tdrop table A
create table A
(
id int,
amount bigint
)
insert A
select 1,40000 union
select 2,70000 union
select 3,80000 union
select 4,90000select T.*,
(select sum(amount) from A where id<=T.id)*0.3,
case when (select sum(amount) from A where id<=T.id)*0.3>40000 then (select sum(amount) from A where id<=T.id)-40000/0.3 end
from A T
declare @t table(ID int,amount int)
insert into @t select 1,40000
insert into @t select 2,70000
insert into @t select 3,80000
insert into @t select 4,90000
--需传入参数
declare @A int
declare @b decimal(18,2)
set @a = 40000
set @b=0.3
--处理语句
declare @id int
select @id=min(id)
from @t a
where (select sum(amount)*@b from @t where id<=a.id)>=@aselect allsum=sum(case when id<=@id then 0 else amount end)+(sum(case when id<=@id then amount else 0 end)-@a/@b )
from @t
where id<=@id or id=(select top 1 id from @t where id>@id order by id desc)/*结果
146666.6666666666666666667*/
--排序修改一下
where id<=@id or id=(select top 1 id from @t where id>@id order by id)
insert into t select 1,40000
insert into t select 2,70000
insert into t select 3,80000
insert into t select 4,90000if exists(select 1 from sysobjects where id=object_id(N'p_getNewValue') and xtype='P')
drop procedure p_getNewValue
go
create procedure p_getNewValue
@A int,
@B decimal(18,2)
as
declare @maxid int
select top 1 @maxid=a.id from t a where (select sum(amount)*@b from t where id<=a.id)<=@a order by a.id desc
print @maxid
select amount=cast((a.amount+(select sum(amount) from t where id<a.id)-@a/@b) as decimal(18,2))
from t a
-- where a.id=@maxid+2 --若ID为连续的可用这个
where a.id=(select top 1 tt.id from (select top 2 id from t where id>@maxid order by id) tt order by tt.id desc)
go
exec p_getNewValue 20000,0.2
exec p_getNewValue 40000,0.3
drop table t