已知1.21
怎样从如下数据中找出与之最接近的来。1.1
1.2
1.3
1.4
1.5
SELECT * FROM a
WHERE (fiH =(SELECT 1.21-MIN(ABS(1.21 - fiH)) AS tmp01
FROM a )) 如果差值为正时,结果正确,如果差值为负,不知该如何来写此语句,哪位帮一下忙?尽可能语句效率高些。
怎样从如下数据中找出与之最接近的来。1.1
1.2
1.3
1.4
1.5
SELECT * FROM a
WHERE (fiH =(SELECT 1.21-MIN(ABS(1.21 - fiH)) AS tmp01
FROM a )) 如果差值为正时,结果正确,如果差值为负,不知该如何来写此语句,哪位帮一下忙?尽可能语句效率高些。
declare @val_2 realset @val_1 = IsNull(
(select top 1 value from table where value > 1.21 order by value desc), 0.00
)
set @val_2 = IsNull(
(select top 1 value from table where value > 1.21 order by value), 0.00
)select
case
when (ABS(@val_1 - 1.21) > ABS(@val_2 - 1.21)) then @val_2
else @val_1
end as result
declare @val_2 realset @val_1 = IsNull(
(select top 1 value from table where value < 1.21 order by value desc), 0.00
)--这里刚才写错了,应该是小于号set @val_2 = IsNull(
(select top 1 value from table where value > 1.21 order by value), 0.00
)select
case
when (ABS(@val_1 - 1.21) > ABS(@val_2 - 1.21)) then @val_2
else @val_1
end as result//或者top 1那里根据实际情况分别用max和min也可以
FROM a )
use pubs
create table test
(
id int ,
value real,
)
insert into test (id,value) values(1,1.1)
insert into test (id,value) values(2,1.2)
insert into test (id,value) values(3,1.3)
insert into test (id,value) values(4,1.4)
insert into test (id,value) values(5,1.5)
insert into test (id,value) values(6,1.6)语句,可查出结果1.2
select top 1 * from test order by abs(value-1.21)
马上结帐.头脑越来越昏. sql.add(' SELECT * FROM S_Gy_E_Xs_Ndcl'); //机型、锭带盘直径
sql.add(' WHERE (Abs('+dxedit1.text+' - fNd) = (SELECT Min(Abs('+dxedit1.text+' - fNd)) AS tmp01 ');
sql.add(' FROM S_Gy_E_Xs_Ndcl where sjx='''+dxedit20.text+''' and fDdpD='+dxedit19.text+' ) and sjx='''+dxedit20.text+''' and fDdpD='+dxedit19.text+') ');