有这样一个表结构 表名A: 字段 ID(guid类型下面是我乱打的) Name(varchar类型)ID Name
-----------------------------------aaa aaaaa
asdf asdf
3333 sdfas
32asfsf asfasaf1
safasf asfdasfsa2
safsaf safasfasf3
asfsaf asfsafsa4
asfddddddddddddddd sfafasfsafsa
asfdsafsaf asfsafsafsasfasdf
cccccccccccc cccccccccsdds
sdsdwe wewe23
xafwf 42323234
现在对这个表进行数据分页 一页显示3条,一共有4页,一共12条数据,现在我有一个方法 通过传入一个ID能够获取以这个ID为中间的 相邻两条数据比如我传一个ID:3333 就可以返回上面相邻的一条数据“asdf” 和下面相邻的一条数据“32asfsf"
请问这样的SQL语句怎么写???
数据库是SQLSERVER 2000
-----------------------------------aaa aaaaa
asdf asdf
3333 sdfas
32asfsf asfasaf1
safasf asfdasfsa2
safsaf safasfasf3
asfsaf asfsafsa4
asfddddddddddddddd sfafasfsafsa
asfdsafsaf asfsafsafsasfasdf
cccccccccccc cccccccccsdds
sdsdwe wewe23
xafwf 42323234
现在对这个表进行数据分页 一页显示3条,一共有4页,一共12条数据,现在我有一个方法 通过传入一个ID能够获取以这个ID为中间的 相邻两条数据比如我传一个ID:3333 就可以返回上面相邻的一条数据“asdf” 和下面相邻的一条数据“32asfsf"
请问这样的SQL语句怎么写???
数据库是SQLSERVER 2000
然后返回
WHERE ID = (CASE WHEN @ID - 1>0 THEN @ID -1 ELSE 1 END) OR ID = (CASE WHEN @ID +1 <=@MaxID THEN @ID + 1 ELSE @MaxID END)
go
select top 2 * from # where ID1<>(select ID1 from # where ID=3333) order by abs(2-ID1)
go
drop #
根据所要传的变量,给这两个变量赋值.
然后where ID in(ID,preID,nextID)这样就行了.
select * from (
select * from A where id = (select man(id) from A where ID<@ID)
union all
select * from A where id = @ID
union all
select * from A where id = (select man(id) from A where ID<@ID)) T
不过你的数据ID列没有规律,
那比较难处理,.
go
declare @nid int
select @nid=nid from tb where id='3333'
select * from tb where nid between @nid-1 and @nid+1
go
alter table tb drop column nid
go
insert @t select 'aaa', ' aaaaa' union all select
'asdf' , 'asdf' union all select
'3333' , 'sdfas' union all select
'32asfsf' , ' asfasaf1' union all select
'safasf' , ' asfdasfsa2' union all select
'safsaf' , ' safasfasf3' union all select
'asfsaf' , ' asfsafsa4' union all select
'asfddddddddddddddd' , ' sfafasfsafsa' union all select
'asfdsafsaf' ,' asfsafsafsasfasdf' union all select
'cccccccccccc' , ' cccccccccsdds' union all select
'sdsdwe' , 'wewe23' union all select
'xafwf' , '42323234'
select top 3 * from @t order by abs((select ID1 from @t where id='3333')-ID1)