现在有id content users
1 XXXXX user1
5 XXXXX user2
10 XXXXX user3
13 XXXXX user2
15 XXXXX user2
20 XXXXX user3这样一个表,求一SQL语句可以得到两条记录(上一条,下一条)
如:
1.当users = user2 ,id=13时,结果为
id content users
--------------------------------
5 XXXXX user2
15 XXXXX user22.当user = user2,id=15时,结果为:
id content users
--------------------------------
13 XXXXX user2
0 0 03.当user=user2,id=5时,结果为
id content users
--------------------------------
0 0 0
13 XXXXX user2
1 XXXXX user1
5 XXXXX user2
10 XXXXX user3
13 XXXXX user2
15 XXXXX user2
20 XXXXX user3这样一个表,求一SQL语句可以得到两条记录(上一条,下一条)
如:
1.当users = user2 ,id=13时,结果为
id content users
--------------------------------
5 XXXXX user2
15 XXXXX user22.当user = user2,id=15时,结果为:
id content users
--------------------------------
13 XXXXX user2
0 0 03.当user=user2,id=5时,结果为
id content users
--------------------------------
0 0 0
13 XXXXX user2
解决方案 »
- 在线等:去除数据中的冗余字段
- 谁能帮我解决这个问题?
- 怎样写一个这样的约束?
- 如何读出SQL Server 中nchar 和 ntext字段的值?
- 想N天的复杂问题,做一个市所有商品导购网的数据库
- 由于没有人回答,第二次问这个问题。后台用的是MS SQL SERVER ,当存进浮点数时,例如0.01,在数据库中显示的是 .01,怎么回事
- DELPHI开发中的问题???(急急急!!!)
- 送分了。。。如何在ACCESS查询中加入序号功能?
- 令我困惑的SQL语句!求助!!
- 请教:大家都说男,女不能做索引,但这个语句,男女不加索引,超时
- 利用BCP将数据库表数据导出到D盘的一个txt文件的问题
- 影片上映档期的查询
select top 1 * from tb
where users = user2 and id<13下一条
select top 1 * from tb
where users = user2 and id>13
上一条:
select top 1 * from tb
where users = user2 and id<13
order by id desc下一条
select top 1 * from tb
where users = user2 and id>13
order by id
union all
select top 1 * from tb where users = user2 ,id>13 desc
insert b_ select 1 ,'XXXXX' ,'user1'
union all select 5 ,'XXXXX' ,'user2'
union all select 10 ,'XXXXX' ,'user3'
union all select 13 ,'XXXXX' ,'user2'
union all select 15 ,'XXXXX', 'user2'
union all select 20 ,'XXXXX' ,'user3'select * from (select top 1 * from (
select * from (select top 1 * from b_ where users='user2' and id<15 order by id desc) aa
union all
select null,null,null
)aaa order by id desc
)aaaaunion all
select * from (
select top 1 * from (
select * from (select top 1 * from b_ where users='user2' and id>15 order by id) aa
union all
select null,null,null
)aaa order by id desc
) bbbb--result
/*id content users
----------- -------------------- --------------------
13 XXXXX user2
NULL NULL NULL(所影响的行数为 2 行)*/
id content users
--------------------------------
13 XXXXX user2
0 0 03.当user=user2,id=5时,当上一条没有时,结果为
id content users
--------------------------------
0 0 0
13 XXXXX user2id content users
-------------------------------
0 0 0是必须有的
select * from(
select 0,0,0
union all
select top 1 * from tb where users = user2 and id<13 order by id desc
union all
select top 1 * from tb where users = user2 and id>13 order by id
union all
select 0,0,0
)t
insert tb select 1 ,'XXXXX' ,'user1'
union all select 5 ,'XXXXX' ,'user2'
union all select 10 ,'XXXXX' ,'user3'
union all select 13 ,'XXXXX' ,'user2'
union all select 15 ,'XXXXX', 'user2'
union all select 20 ,'XXXXX' ,'user3'select * from(
select * from
(
select convert(varchar(10),0) id,convert(varchar(10),0) content,convert(varchar(10),0) users
union all
select top 1 * from tb where users = 'user2' and id<13 order by id desc) t1
where id<>0 or (id=0 and not exists(select 1 from tb where users = 'user2' and id<13))
union all
select * from
(
select * from
(select top 1 * from tb where users = 'user2' and id>13 order by id) f
union all
select convert(varchar(10),0) id,convert(varchar(10),0) content,convert(varchar(10),0) users
)t2
where id<>0 or (id=0 and not exists(select 1 from tb where users = 'user2' and id>13))
) t
/*id content users
----------- -------------------- --------------------
5 XXXXX user2
15 XXXXX user2
(所影响的行数为 2 行)
*/select * from(
select * from
(
select convert(varchar(10),0) id,convert(varchar(10),0) content,convert(varchar(10),0) users
union all
select top 1 * from tb where users = 'user2' and id<5 order by id desc) t1
where id<>0 or (id=0 and not exists(select 1 from tb where users = 'user2' and id<5))
union all
select * from
(
select * from
(select top 1 * from tb where users = 'user2' and id>5 order by id) f
union all
select convert(varchar(10),0) id,convert(varchar(10),0) content,convert(varchar(10),0) users
)t2
where id<>0 or (id=0 and not exists(select 1 from tb where users = 'user2' and id>5))
) t
/*id content users
id content users
----------- -------------------- --------------------
0 0 0
13 XXXXX user2
(所影响的行数为 2 行)
*/
select * from(
select * from
(
select convert(varchar(10),0) id,convert(varchar(10),0) content,convert(varchar(10),0) users
union all
select top 1 * from tb where users = 'user2' and id<15 order by id desc) t1
where id<>0 or (id=0 and not exists(select 1 from tb where users = 'user2' and id<15))
union all
select * from
(
select * from
(select top 1 * from tb where users = 'user2' and id>15 order by id) f
union all
select convert(varchar(10),0) id,convert(varchar(10),0) content,convert(varchar(10),0) users
)t2
where id<>0 or (id=0 and not exists(select 1 from tb where users = 'user2' and id>15))
) t
/*
id content users
----------- -------------------- --------------------
13 XXXXX user2
0 0 0
(所影响的行数为 2 行)*/
刚才,CSDN升级,我只能看到七楼。