输入一个值是200搜索表与200最接近的值。
例表数据有
列
100
300
想查找出与200最小绝对值的是那个？怎么计算呢？
------解决方案--------------------
declare @id int
set @id=20
select MIN(id)
from
(
select top 1 *
from (select *,rn=ROW_NUMBER() over(order by id) from MobileDevice where id>@id)t
where rn=1
union all
select top 1 *
from (select *,rn=ROW_NUMBER() over(order by id desc) from MobileDevice where id<@id)t
where rn=1
)a
------解决方案--------------------
-- 上面忘记加了绝对值
declare @id int
set @id=200
select MIN(abs(id))
from
(
select top 1 *
from (select *,rn=ROW_NUMBER() over(order by id) from MobileDevice where id>@id)t
where rn=1
union all
select top 1 *
from (select *,rn=ROW_NUMBER() over(order by id desc) from MobileDevice where id<@id)t
where rn=1
)a
------解决方案--------------------
create table #tb(id int)
insert into #tb values(100)
insert into #tb values(300)
declare @id int
set @id=200
select MIN(abs(id)) as result
from
(
select top 1 *
from (select *,rn=ROW_NUMBER() over(order by id) from #tb where id>@id)t
where rn=1
union all
select top 1 *
from (select *,rn=ROW_NUMBER() over(order by id desc) from #tb where id<@id)t
where rn=1
)a
/*
result
100
*/