日期:2014-05-18  浏览次数:20452 次

要求写个标量值函数
废话不多说直接举例子:

参数值:33.3

表 table1:
Count Price
11 18000
9 17000
17 16000
100 15000
100 14000


结果:
(11*18000 + 9*17000 + 13.3*16000)/33.3= 16930.93 返回这个结果

意思就是说循环表tabel1 当33.3大于时就继续循环 小于等于0时退出循环 返回结果

------解决方案--------------------
游标
------解决方案--------------------
我觉得你需要加上个序号,来进行先进先出.
SQL code
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