考虑可能有多个值都与所输入的值相近,可以改为: select * from tb where abs(b-1.35)=(select top 1 min(abs(b-1.35)) from tb) 或 select * from tb where abs(b-1.35) in (select min(abs(b-1.35)) from tb)
sorry, 考虑欠佳,应该 select * from tb where abs(b-1.35)=(select min(abs(b-1.35)) from tb) 就行了,呵呵!
create table tab1 ( a int, b dec(14,2) ) insert into tab1 values(10,1.2) insert into tab1 values(20,1.3) insert into tab1 values(30,1.5) insert into tab1 values(40,2.4) --drop table tab1create proc proc_abc@s dec(14,2) --给出的数,如 1.35 asdeclare @a dec(14,2) --行select @a=a from tab1 where b < @s if @@error <> 0 begin select -200,'fail 1' return endselect a,b from tab1 where b > @s and a=@a + 10 if @@error <> 0 begin select -300,'fail 2' return end select @a out ---exec proc_abc 1.35 --a b ----------- ---------------- 30 1.50(所影响的行数为 1 行)out ---------------- 20.00(所影响的行数为 1 行)
--测试环境 create table ta (a int,b decimal(5,2) ) go insert ta select 10,1.2 union all select 20,1.3 union all select 30,1.5 union all select 40,2.0 goCREATE FUNCTION testDec (@num decimal(5,2)) RETURNS decimal(12,9) AS BEGIN declare @max int,@min int declare @maxD decimal(5,2),@minD decimal(5,2) declare @return decimal(12,9) select top 1 @max=a,@maxD=b from ta where b>@num order by b asc select top 1 @min=a,@minD=b from ta where b<@num order by b desc select @return=@min+(@max-@min)*((@num-@minD)/(@maxD-@minD)) RETURN(@return) END-- 测试 select isnull(dbo.testDec(1.35),0)--收尾 drop function testDec drop table ta --结果 -------------- 22.500000000(所影响的行数为 1 行)
create table tab1 ( a int, b dec(14,2) ) insert into tab1 values(10,1.2) insert into tab1 values(20,1.3) insert into tab1 values(30,1.5) insert into tab1 values(40,2.4) --drop table tab1alter proc proc_abc@s dec(14,2) --给出的数,如 1.35 asdeclare @a dec(14,2) --行select @a=a from tab1 where b < @s if @@error <> 0 begin select -200,'fail 1' return endselect (2*a-10)/2 from tab1 where b > @s and a=@a + 10 if @@error <> 0 begin select -300,'fail 2' return end---exec proc_abc 1.35 --结果
create table tab1 ( a int, b dec(14,2) ) insert into tab1 values(10,1.2) insert into tab1 values(20,1.3) insert into tab1 values(30,1.5) insert into tab1 values(40,2.4) --drop table tab1alter proc proc_abc@s dec(14,2) --给出的数,如 1.35 asdeclare @a dec(14,2) --行select @a=a from tab1 where b < @s if @@error <> 0 begin select -200,'fail 1' return endselect (2*a-10)/2 from tab1 where b > @s and a=@a + 10 if @@error <> 0 begin select -300,'fail 2' return end---exec proc_abc 1.35 --结果
select distinct (1.35 - (select top 1 b from t where b < 1.35 order by b desc)) * ((select top 1 a from t where b > 1.35 order by b asc) - (select top 1 a from t where b < 1.35 order by b desc)) / ((select top 1 b from t where b > 1.35 order by b asc) - (select top 1 b from t where b < 1.35 order by b desc)) + (select top 1 a from t where b < 1.35 order by b desc) as 结果 from t
select * from tb
where abs(b-1.35)=(select top 1 min(abs(b-1.35)) from tb)
或
select * from tb
where abs(b-1.35) in (select min(abs(b-1.35)) from tb)
select * from tb
where abs(b-1.35)=(select min(abs(b-1.35)) from tb)
就行了,呵呵!
create table tab1
(
a int,
b dec(14,2)
)
insert into tab1 values(10,1.2)
insert into tab1 values(20,1.3)
insert into tab1 values(30,1.5)
insert into tab1 values(40,2.4)
--drop table tab1create proc proc_abc@s dec(14,2) --给出的数,如 1.35
asdeclare @a dec(14,2) --行select @a=a from tab1 where b < @s if @@error <> 0
begin
select -200,'fail 1'
return
endselect a,b from tab1 where b > @s and a=@a + 10
if @@error <> 0
begin
select -300,'fail 2'
return
end
select @a out
---exec proc_abc 1.35
--a b
----------- ----------------
30 1.50(所影响的行数为 1 行)out
----------------
20.00(所影响的行数为 1 行)
create table ta (a int,b decimal(5,2) )
go insert ta select 10,1.2
union all select 20,1.3
union all select 30,1.5
union all select 40,2.0
goCREATE FUNCTION testDec (@num decimal(5,2))
RETURNS decimal(12,9)
AS
BEGIN
declare @max int,@min int
declare @maxD decimal(5,2),@minD decimal(5,2)
declare @return decimal(12,9) select top 1 @max=a,@maxD=b from ta where b>@num order by b asc
select top 1 @min=a,@minD=b from ta where b<@num order by b desc
select @return=@min+(@max-@min)*((@num-@minD)/(@maxD-@minD))
RETURN(@return)
END-- 测试
select isnull(dbo.testDec(1.35),0)--收尾
drop function testDec
drop table ta --结果
--------------
22.500000000(所影响的行数为 1 行)
(
a int,
b dec(14,2)
)
insert into tab1 values(10,1.2)
insert into tab1 values(20,1.3)
insert into tab1 values(30,1.5)
insert into tab1 values(40,2.4)
--drop table tab1alter proc proc_abc@s dec(14,2) --给出的数,如 1.35
asdeclare @a dec(14,2) --行select @a=a from tab1 where b < @s if @@error <> 0
begin
select -200,'fail 1'
return
endselect (2*a-10)/2 from tab1 where b > @s and a=@a + 10
if @@error <> 0
begin
select -300,'fail 2'
return
end---exec proc_abc 1.35
--结果
-----------
25(所影响的行数为 1 行)
--不过你的a 列要是以10递增的,如10,20,30,40,50。。
(
a int,
b dec(14,2)
)
insert into tab1 values(10,1.2)
insert into tab1 values(20,1.3)
insert into tab1 values(30,1.5)
insert into tab1 values(40,2.4)
--drop table tab1alter proc proc_abc@s dec(14,2) --给出的数,如 1.35
asdeclare @a dec(14,2) --行select @a=a from tab1 where b < @s if @@error <> 0
begin
select -200,'fail 1'
return
endselect (2*a-10)/2 from tab1 where b > @s and a=@a + 10
if @@error <> 0
begin
select -300,'fail 2'
return
end---exec proc_abc 1.35
--结果
-----------
25(所影响的行数为 1 行)
--不过你的a 列要是以10递增的,如10,20,30,40,50。。
(1.35 - (select top 1 b from t where b < 1.35 order by b desc)) *
((select top 1 a from t where b > 1.35 order by b asc) - (select top 1 a from t where b < 1.35 order by b desc)) /
((select top 1 b from t where b > 1.35 order by b asc) - (select top 1 b from t where b < 1.35 order by b desc)) +
(select top 1 a from t where b < 1.35 order by b desc) as 结果
from t