请教各位, 比如 表A中 数据如下:
id column1 column2
2 aa bb
3 cc dd
5 dd ff
9 dd cc 如果我想 查id=5 的上一条和下一条数据,还有id=5的 ,一共三条 <5的一条, =5的一条 >5的一条 这SQL 要咋写?
id column1 column2
2 aa bb
3 cc dd
5 dd ff
9 dd cc 如果我想 查id=5 的上一条和下一条数据,还有id=5的 ,一共三条 <5的一条, =5的一条 >5的一条 这SQL 要咋写?
from (select top 1 * from a where ID<5 order by ID desc) k
union all
select *
from (select top 1 * from a where ID>5 order by ID) k
SQL2005 就用ROW_NUMBER()吧
set @id=5select * from T
where id=(select top 1 id from T where id<@id order by id desc)
union all
select * from T
where id=@id
union all
select * from T
where id=(select top 1 id from T where id>@id order by id)
(select rn=ROW_NUMBER()over(order by id),* from a )
select id ,column1,column2
from cte
where rn=(select rn from cte where ID=5)-1 or rn=(select rn from cte where ID=5)+1
LZ没说清楚
UNION 的话 应该就不用考虑了吧
(
id int,
column1 varchar(20),
column2 varchar(20)
)
insert into #TT select 2,'aa','bb'
insert into #TT select 3,'cc','dd'
insert into #TT select 5,'dd','ff'
insert into #TT select 9,'dd','cc'
with hgo as
(
select *,
(select count(0)+1 from #TT where id<T.id) rank
from #TT T
)
select id,column1,column2 from hgo
where rank=(select rank from hgo where ID=5)-1 or rank=(select rank from hgo where ID=5)+1
id column1 column2
----------- -------------------- --------------------
3 cc dd
9 dd cc
if OBJECT_ID('t') is not null
drop table t
Create table t(id int,column1 varchar(10),column2 varchar(10))
insert into t select 2,'aa','bb' union all
select 3,'cc','dd' union all
select 5,'dd','ff' union all
select 9,'dd','cc' union all
select 10,'dd','cc'
select *from
(select top 1 * from t where id >5 order by id) a union all
(select * from t where id =5 )union all
select * from
(select top 1 * from t where id <5 order by id desc)b