已知表中的id，id不一定连续，中间可能被删除，求数据库的上一条记录，下一条记录
假设表表A（id,a)
有数据（1，“kkk")
（2，“kksdk")
(3，“kksdfk")
（5，“kkddk")
现在已知数据id=3,求查询id的上一条记录，和后一条记录
------解决方案--------------------
上
select top 1
from t
where id<3
order by id desc
下
select top 1
from t
where id>3
order by id
------解决方案--------------------
用两个exists来判断
------解决方案--------------------
如果是2005及以上，用row_number很容易查询
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-06-26 17:14:55
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[col] varchar(2))
insert [tb]
select 1,'aa' union all
select 3,'bb' union all
select 5,'cc' union all
select 7,'dd'
--------------开始查询--------------------------
;with f as
(
select *,ROW_NUMBER()over(order by getdate()) as px from tb
)
select id,col from f where px=
(
select
px-1
from
f
where
id=3)
union all
select id,col from f where px=
(
select
px+1
from
f
where
id=3)
----------------结果----------------------------
/* id col
----------- ----
1 aa
5 cc
(2 行受影响)
*/