alter function fn_价格(
@BeginGrade int,
@EndGrade int
)
returns numeric(10,2)
as
begin
declare @r numeric(10,2)
set @r=0
--1
select @r=@r+Price from 分级价格表 where BeginGrade>=@BeginGrade-1
and EndGrade<=@EndGrade
--2
select @r=@r+Price*(EndGrade-@BeginGrade)/(EndGrade-BeginGrade) from 分级价格表 where BeginGrade<@BeginGrade
and EndGrade>=@BeginGrade
--3
select @r=@r+Price*(@EndGrade-BeginGrade)/(EndGrade-BeginGrade) from 分级价格表 where BeginGrade<@EndGrade
and EndGrade>=@EndGrade
alter function fn_价格(
@BeginGrade int,
@EndGrade int
)
returns numeric(10,2)
as
begin
declare @r numeric(10,2)
set @r=0
--1
select @r=@r+Price from 分级价格表 where BeginGrade>@BeginGrade
and EndGrade<=@EndGrade
--2
select @r=@r+Price*(EndGrade-@BeginGrade)/(EndGrade-BeginGrade) from 分级价格表 where BeginGrade<@BeginGrade
and EndGrade>=@BeginGrade
--3
select @r=@r+Price*(@EndGrade-BeginGrade)/(EndGrade-BeginGrade) from 分级价格表 where BeginGrade<@EndGrade
and EndGrade>=@EndGrade
insert 分级价格表
select
0 , 20 , 80
union all select
20 , 24 , 75
union all select
24 , 28 , 100
go
create function fn_价格(
@BeginGrade int,
@EndGrade int
)
returns numeric(10,2)
as
begin
declare @r numeric(10,2)
set @r=0
--1
select @r=@r+Price from 分级价格表 where BeginGrade>=@BeginGrade
and EndGrade<@EndGrade
--2
select @r=@r+Price*(EndGrade-@BeginGrade)/(EndGrade-BeginGrade) from 分级价格表 where BeginGrade<=@BeginGrade
and EndGrade>@BeginGrade
--3
select @r=@r+Price*(@EndGrade-BeginGrade)/(EndGrade-BeginGrade) from 分级价格表 where BeginGrade<=@EndGrade
and EndGrade>@EndGrade
create function fn_价格1(
@BeginGrade int,
@EndGrade int
)
returns numeric(10,2)
as
begin
declare @r numeric(10,2)
select @r=sum(
case when @BeginGrade > begingrade and @BeginGrade <= endgrade and @EndGrade > begingrade and @EndGrade <= endgrade
then (@endgrade - @BeginGrade+1)
when @BeginGrade > begingrade and @BeginGrade <= endgrade
then (endgrade - @BeginGrade+1)
when @EndGrade > begingrade and @EndGrade <= endgrade
then (@EndGrade - begingrade)
else (endgrade - begingrade)
end * price/(endgrade - begingrade)
)
from 分级价格表 a
where @BeginGrade > begingrade and @BeginGrade <= endgrade
or @BeginGrade <begingrade and @EndGrade >= endgrade
or @EndGrade > begingrade and @EndGrade <= endgrade
create function fn_价格1(
@BeginGrade int,
@EndGrade int
)
returns numeric(10,2)
as
begin
declare @r numeric(10,2)
select @r=sum(
case when @BeginGrade > begingrade and @BeginGrade <= endgrade and @EndGrade > begingrade and @EndGrade <= endgrade
then (@endgrade - @BeginGrade+1)
when @BeginGrade > begingrade and @BeginGrade <= endgrade
then (endgrade - @BeginGrade)
when @EndGrade > begingrade and @EndGrade <= endgrade
then (@EndGrade - begingrade)
else (endgrade - begingrade)
end * price/(endgrade - begingrade)
)
from 分级价格表 a
where @BeginGrade > begingrade and @BeginGrade <= endgrade
or @BeginGrade <begingrade and @EndGrade >= endgrade
or @EndGrade > begingrade and @EndGrade <= endgrade
declare @b int
declare @e int
set @b = 8
set @e = 25
select sum(
case when @b >= begingrade and @b < endgrade
then (endgrade - @b)
when @e >= begingrade and @e < endgrade
then (@e - begingrade)
else (endgrade - begingrade)
end * price/(endgrade - begingrade)
)
from 分级价格表 a
where @b >= begingrade and @b < endgrade
or @b <=begingrade and @e > endgrade
or @e >= begingrade and @e < endgrade