create table test (ID int) insert test values (1) insert test values (3) insert test values (6) insert test values (8) insert test values (9) insert test values (10) --select * from test gocreate proc pTest @id int as begin select * from test where id=(select max(id) from test where id<@id) or id=(select min(id) from test where id>@id) end go--我现在知道ID=8的这条记录,现在想取出6和9,即8两边的那两记录 exec pTest 8drop proc pTest drop table test
select max(id) from test where id<8 union select min(id) from test where id>8
select * from tablename where (id =(select max(id) from tablename where id<8)) or (id =(select min(id) from tablename where id>8))
select *,identity(int,1,1) #temp from tableNameselect * from #temp where TID=((select TID from #Temp where id=8)-1) or TID=((select TID from #Temp where id=8)+1) drop table #temp
insert test values (1)
insert test values (3)
insert test values (6)
insert test values (8)
insert test values (9)
insert test values (10)
--select * from test
gocreate proc pTest
@id int
as
begin
select * from test
where
id=(select max(id) from test where id<@id)
or
id=(select min(id) from test where id>@id)
end
go--我现在知道ID=8的这条记录,现在想取出6和9,即8两边的那两记录
exec pTest 8drop proc pTest
drop table test
union
select min(id) from test where id>8
where
(id =(select max(id) from tablename where id<8))
or
(id =(select min(id) from tablename where id>8))
where TID=((select TID from #Temp where id=8)-1)
or TID=((select TID from #Temp where id=8)+1)
drop table #temp