查询相邻记录的问题？
示例记录：
ID   Name
1     John
2     Kary
3     Betty
4     Joe
5     Tony
===========
以上形式中ID是主键
现在需求是，已知某个ID的记录值则查询返回前后相邻的记录（包含自身），如果是第一条记录则只返回自身和后一条记录即可，如果是末尾的记录则返回上一条记录和自身即可。
如查询ID=4的记录则返回：
3     Betty
4     Joe
5     Tony
===========
有没有能用一句Sql查询语句即可获取的办法？优先考虑这一方案，其次为存储过程。注意：需要考虑记录海量时的查询性能问题；ID的编号不一定是连续的。


------解决方案--------------------
create table tab(ID int, Name varchar(10))
insert tab
select 1 , 'John '
union all select 2 , 'Kary '
union all select 3, 'Betty '
union all select 4 , 'Joe '
union all select 5 , 'Tony '

select top 1 id,name from tab where id <4 order by id desc

select id,name from tab where id=4

select top 1 id,name from tab where id> 4 order by id

drop table tab
------解决方案--------------------
Create Procedure SP_TEST(@ID Int)
As
Select * From ( Select TOP 1 * From TEST Where ID < @ID Order By ID Desc) A
Union All
Select * From TEST Where ID = @ID
Union All
Select * From ( Select TOP 1 * From TEST Where ID > @ID Order By ID ) B
GO
EXEC SP_TEST 4
------解决方案--------------------
我來試試,嘿嘿

declare @t table(
id int,
name varchar(10))

insert @t select 1, 'John '
union all select 3, 'Kary '
union all select 5, 'Betty '
union all select 7, 'Joe '
union all select 9, 'Tony '

select * from @t
where id = (select top 1 id from @t where id < 7 order by id desc)
union all
select * from @t
where id = 7
union all
select * from @t
where id = (select top 1 id from @t where id > 7)

/*
id name
----------- ----------
5 Betty
7 Joe
9 Tony

（所影响的行数为 3 行）
*/
------解决方案--------------------

create table tab(ID int, Name varchar(10))
insert tab
select 1 , 'John '
union all select 2 , 'Kary '
union all select 3, 'Betty '
union all select 4 , 'Joe '
union all select 5 , 'Tony '

select * from tab where id=(select max(id) from tab where id <4)
union all
select id,name from tab where id=4
union all
select * from tab where id=(select min(id) from tab where id> 4)

drop table tab
------解决方案--------------------
declare @t table(
id int,
name varchar(10))

insert @t select 1, 'John '
union all select 3, 'Kary '
union all select 5, 'Betty '
union all select 7, 'Joe '
union all select 9, 'Tony '

select * from @t a where id = 7
or not exists (
select 1 from @t where id> 7 and id <a.id
) and a.id> 7
or not exists (
select 1 from @t where id <7 and id> a.id
) and a.id <7

--
id name
----------- ----------
5 Betty
7 Joe
9 Tony

（所影响的行数为 3 行）


------解决方案--------------------
select * from t where id in((select min(id) from t where id > 4),select max(id) from t where id <4),4)