select top 1 * from [Table] where id<'FA099' order by id desc select top 1 * from [Table] where id>'FA099' order by id
select * from ( select top 1 * from tab where id>'FA099' order by id asc ) as t union all select * from ( select top 1 * from tab where id<'FA099' order by id desc ) as t1
select min(key)from tb where key >'FA099' union select max(key)from tb where key <'FA099'
--> 测试数据: @s declare @s table (ID varchar(5)) insert into @s select 'FA001' union all select 'FA002' union all select 'FA003' union all select 'FA099' union all select 'FA999'declare @id varchar(20) set @id='FA099' select px=(select count(1) from @s where cast(right(id,3) as int)<=cast(right(a.id,3) as int)),* into # from @s a select 上笔=b.id,下笔=e.id,a.* from # a left join # b on a.px=b.px+1 left join # e on a.px=e.px-1 where a.id=@id--结果: 上笔 下笔 px ID ----- ----- ----------- ----- FA003 FA999 4 FA099(1 行受影响)
select top 1 * from [Table] where id>'FA099' order by id
from (
select top 1 * from tab
where id>'FA099'
order by id asc
) as t
union all
select *
from (
select top 1 * from tab
where id<'FA099'
order by id desc
) as t1
union select max(key)from tb where key <'FA099'
--> 测试数据: @s
declare @s table (ID varchar(5))
insert into @s
select 'FA001' union all
select 'FA002' union all
select 'FA003' union all
select 'FA099' union all
select 'FA999'declare @id varchar(20)
set @id='FA099'
select px=(select count(1) from @s where cast(right(id,3) as int)<=cast(right(a.id,3) as int)),* into # from @s a
select 上笔=b.id,下笔=e.id,a.* from # a
left join # b on a.px=b.px+1
left join # e on a.px=e.px-1
where a.id=@id--结果:
上笔 下笔 px ID
----- ----- ----------- -----
FA003 FA999 4 FA099(1 行受影响)