日期:2014-05-18 浏览次数:20452 次
create table table1(id int,[Count] int, Price int) insert into table1 values(1,11 ,18000) insert into table1 values(2,9 ,17000) insert into table1 values(3,17 ,16000) insert into table1 values(4,100 ,15000) insert into table1 values(5,100 ,14000) go declare @cnt as decimal(18,1) set @cnt = 33.3 select cast(sum([count]*price)/@cnt as decimal(18,2)) from ( select (case when (select sum([count]) from table1 where id <= t.id) <= @cnt then [count] when (select sum([count]) from table1 where id <= t.id) - [count] > @cnt then 0 else @cnt + [count] - (select sum([count]) from table1 where id <= t.id) end) [count], price from table1 t ) m /* -------------------- 16930.93 (所影响的行数为 1 行) */ drop table table1