用臨時表好像也可以 declare @n int select [id]=identity(int,1,1),* into #temp from T set @n=(select [id] from #temp where XX=XX) select * from #temp where [id] in (@n-1,@n,@n+1)
楼上的写法可以是 select top 2 * from #temp where [id]<>@n ORDER BY ABS(ID-@n)我也来写一个。 DECLARE @tb TABLE(id INT,c INT) INSERT @tb SELECT 1,00001 UNION SELECT 3,00003 UNION SELECT 9,00120 UNION SELECT 10,09211 UNION SELECT 11,2542 DECLARE @id INT SET @id=9 SELECT * FROM @tb a WHERE id>@id AND 1>(SELECT COUNT(1) FROM @tb b WHERE b.id<a.id AND b.id>@id) OR id<@id AND 1>(SELECT COUNT(1) FROM @tb b WHERE b.id>a.id AND b.id<@id)
----借用楼上的测试数据,看看这个怎么样: DECLARE @tb TABLE(id INT,c INT) INSERT @tb SELECT 1,00001 UNION SELECT 3,00003 UNION SELECT 9,00120 UNION SELECT 10,09211 UNION SELECT 11,2542 select * from @tb DECLARE @id INT SET @id=9 select * from @tb ----同时获得指定ID的前后二个记录的ID select * from ( select top 1 id,c from @tb where id < @id order by id desc union all select top 1 id,c from @tb where id > @id order by id asc ) t
declare @tb1 table ( id int, name varchar(50) ) declare @tb2 table ( id int, name varchar(50) ) insert @tb1 select 3,'test' union select 8,'abc' union select 10,'esadffg' union select 7,'sfd' union select 2,'esfdfg' union select 9,'esafdfg' declare @id int set @id=8 insert @tb2 select top 1 id,name from @tb1 where id < @id order by id desc insert @tb2 select top 1 id,name from @tb1 where id > @id order by id asc select * from @tb2
感觉要用playwarcraft的方法,用临时表,因为并不是指order by 之后的顺序,且id字段也不一定有固定的顺序。
declare @n int
select [id]=identity(int,1,1),* into #temp from T
set @n=(select [id] from #temp where XX=XX)
select * from #temp where [id] in (@n-1,@n,@n+1)
select top 2 * from #temp where [id]<>@n ORDER BY ABS(ID-@n)我也来写一个。
DECLARE @tb TABLE(id INT,c INT)
INSERT @tb
SELECT 1,00001
UNION SELECT 3,00003
UNION SELECT 9,00120
UNION SELECT 10,09211
UNION SELECT 11,2542
DECLARE @id INT
SET @id=9
SELECT * FROM @tb a
WHERE id>@id AND 1>(SELECT COUNT(1) FROM @tb b WHERE b.id<a.id AND b.id>@id)
OR
id<@id AND 1>(SELECT COUNT(1) FROM @tb b WHERE b.id>a.id AND b.id<@id)
DECLARE @tb TABLE(id INT,c INT)
INSERT @tb
SELECT 1,00001
UNION SELECT 3,00003
UNION SELECT 9,00120
UNION SELECT 10,09211
UNION SELECT 11,2542
select * from @tb
DECLARE @id INT
SET @id=9
select * from @tb
----同时获得指定ID的前后二个记录的ID
select * from
(
select top 1 id,c from @tb where id < @id order by id desc union all
select top 1 id,c from @tb where id > @id order by id asc
) t
(
id int,
name varchar(50)
)
declare @tb2 table
(
id int,
name varchar(50)
)
insert @tb1
select 3,'test'
union select 8,'abc'
union select 10,'esadffg'
union select 7,'sfd'
union select 2,'esfdfg'
union select 9,'esafdfg'
declare @id int
set @id=8
insert @tb2
select top 1 id,name from @tb1 where id < @id order by id desc
insert @tb2
select top 1 id,name from @tb1 where id > @id order by id asc
select * from @tb2