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
先乘以一个-1,然后与200相减,再用函数ABS()来获取min值
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 */
DECLARE @v INT SET @v=200;WITH T AS ( SELECT 100 AS [Value] UNION ALL SELECT 1012 UNION ALL SELECT 300 UNION ALL SELECT 501 UNION ALL SELECT 401 UNION ALL SELECT 99 ),TB AS ( SELECT *,RANK() OVER (ORDER BY ABS([Value]-@v)) rk FROM T )SELECT [Value] FROM TB WHERE rk=1--Value --300 --100
DECLARE @v INT SET @v=200;WITH T AS ( SELECT 100 AS [Value] UNION ALL SELECT 1012 UNION ALL SELECT 300 UNION ALL SELECT 501 UNION ALL SELECT 300 UNION ALL SELECT 99 union all select 100 )select * from t where ABS(@v - Value) = (select MIN(ABS(@v - Value)) from t) /* value 100 300 300 100 */
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
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
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
*/
SET @v=200;WITH T AS
(
SELECT 100 AS [Value] UNION ALL
SELECT 1012 UNION ALL
SELECT 300 UNION ALL
SELECT 501 UNION ALL
SELECT 401 UNION ALL
SELECT 99
),TB AS
(
SELECT *,RANK() OVER (ORDER BY ABS([Value]-@v)) rk
FROM T
)SELECT [Value]
FROM TB
WHERE rk=1--Value
--300
--100
SET @v=200;WITH T AS
(
SELECT 100 AS [Value] UNION ALL
SELECT 1012 UNION ALL
SELECT 300 UNION ALL
SELECT 501 UNION ALL
SELECT 300 UNION ALL
SELECT 99 union all
select 100
)select *
from t
where ABS(@v - Value) = (select MIN(ABS(@v - Value)) from t)
/*
value
100
300
300
100
*/