日期:2014-05-18 浏览次数:20552 次
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