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