create function f_1(@num money)
returns int
as
begin
declare @level
set @level=(select min(level) from table1 where pricelevel>=@num)
return @level
end
goselect dbo.f_1(543.67)
/*
3
*/
returns int
as
begin
declare @level
set @level=(select min(level) from table1 where pricelevel>=@num)
return @level
end
goselect dbo.f_1(543.67)
/*
3
*/
returns int
as
begin
declare @level
set @level=(select top 1 level from table1 order by abs(pricelevel-@num))
return @level
end
go
(
PriceLevel decimal(12,2),
Level int
)
insert @PriceLevel
select 10.00,1 union
select 100.00,2 union
select 1000.00,3 union
select 2000.00,4--查询
declare @price money --任意价格
set @price=543.67select min(Level)
from @PriceLevel
where PriceLevel>=@price --结果
/*
-----------
3.00(所影响的行数为 1 行)
*/
create function f_1(@num money)
returns int
as
begin
declare @level
set @level=(select top 1 level from table1 order by abs(pricelevel-@num))
return @level
end
go
order by abs(pricelevel-@num)是取的离@num最近的level,即2,而不是楼主要求的3