sql 语句如何选到上一数据或者下一条数据

[happydaily]

自动列肯定不行了，删除的数据就不连续了，自己定的编号有时也不是连续的，如何办啊

[王向飞]

你取上下数据的依据是什么？

[Linares]

select top 1 * from tb where id<curent_id order by id desc --pre
select top 1 * from tb where id>curent_id order by id asc --next

[叶子]

declare @table table (id int,col varchar(1))
insert into @table
select 1,'a' union all
select 4,'b' union all
select 7,'c' union all
select 9,'d'

--例如参数为4
DECLARE @i INT
SET @i=4
;WITH maco AS(select ROW_NUMBER() OVER (ORDER BY id) AS rowid,id, col from @table )
--得到下一条
select id,col FROM maco WHERE 
rowid=(SELECT rowid+1 FROM maco WHERE id=@i)
/*
id          col
----------- ----
7           c
*/

上一条就把rowid+1改成rowid-1

[Shawn]

create table #temp
(
	id int,
	col varchar(100)
)
insert #temp
select 1,'a' union all
select 4,'b' union all
select 7,'c' union all
select 9,'d' union ALL
select 10,'e'

SELECT * FROM #temp a
OUTER APPLY
(SELECT TOP(1) preid = id, precol = col FROM #temp WHERE id < a.id ORDER BY id DESC) b
OUTER APPLY
(SELECT TOP(1) nextid = id, nextcol = col FROM #temp WHERE id > a.id ORDER BY id) c
ORDER BY a.id

[feixianxxx]

1.游标可以做到..游标用法

2.自己创造一列顺序列.
例：sql server2005的row_number()+CTE

;with cte as
(
select * ,rn=row_number()over(order by getdate()) from tb;
)
select *
from cte c join (
select rn from cte  where col='特定值') b c.rn<>b.rn
where c.rn between b.rn-1 and b.rn+1

[gogodiy]

上一条数据或者下一条数据，总有个比较依据吧，不然如何确定呢。
有了比较依据不就好办了，-1就是上一条，+1就是下一条。当然这个比较依据最好是INT型。

[fwacky]

declare @table table(id int,name varchar(10))
insert into @table
select '8900','aaaa'union all
select '233','aaaa'union all
select '223','aaaa'union all
select '4','aaaa'

;with table1 as  (select rowID=ROW_NUMBER() over(order by getdate()),* from @table)
select * from table1 
where (select rowID from table1 where id=223) between rowID-1 and rowID+1
and rowID<>(select rowID from table1 where id=223)
=========================
rowID                id          name
-------------------- ----------- ----------
2                    233         aaaa
4                    4           aaaa

(2 行受影响)