declare @cvalue int
set @cvalue=150
select top 1
case when exists(select * from t where cvalue=@cvalue) then
(select top 1 ccode from t where cvalue=@cvalue)
else
case when exists(select cvalue from t where cvalue<@cvalue) then
(select top 1 ccode from t where cvalue<@cvalue order by cvalue desc)
else (select top 1 ccode from t order by cvalue)
end
end cvalue
from t
set @cvalue=150
select top 1
case when exists(select * from t where cvalue=@cvalue) then
(select top 1 ccode from t where cvalue=@cvalue)
else
case when exists(select cvalue from t where cvalue<@cvalue) then
(select top 1 ccode from t where cvalue<@cvalue order by cvalue desc)
else (select top 1 ccode from t order by cvalue)
end
end cvalue
from t
declare @cvalue int
set @cvalue=450select max(ccode)from(
select cvalue,ccode from 表
union all
select top 1 cvalue=@cvalue-1,ccode from 表 order by cvalue
union all
select top 1 cvalue=@cvalue+1,ccode from 表 order by cvalue desc
)a where cvalue<@cvalue
create table 表(ctype int,cvalue int,ccode int)
insert 表 select 2,100,5
union all select 2,200,7
union all select 2,250,8
union all select 2,300,9
union all select 2,400,10
go--查询
declare @cvalue int
set @cvalue=450select max(ccode)from(
select cvalue,ccode from 表
union all
select top 1 cvalue=@cvalue-1,ccode from 表 order by cvalue
union all
select top 1 cvalue=@cvalue+1,ccode from 表 order by cvalue desc
)a where cvalue<@cvalue
go--删除测试环境
drop table 表/*--测试结果
-----------
10(所影响的行数为 1 行)
--*/