求一SQL语句 关于上一条，下一条信息

[fireman_me]

现在有

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 user2

2.当user = user2,id=15时，结果为：
id content users
--------------------------------
13 XXXXX user2
0 0 0

3.当user=user2,id=5时，结果为
id content users
--------------------------------
0 0 0
13 XXXXX user2

[cailee]

上一条：
select top 1 * from tb
where users = user2 and id<13

下一条
select top 1 * from tb
where users = user2 and id>13

[cailee]

[Quote=引用 1 楼 cailee 的回复:]
上一条：
select top 1 * from tb
where users = user2 and id<13

下一条
select top 1 * from tb
where users = user2 and id>13
[/Quote]
错了。修改下。
上一条：
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

[fireman_me]

[Quote=引用 2 楼 cailee 的回复:]

引用 1 楼 cailee 的回复:
上一条：
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 = u……
[/Quote]

要一句代码，不是分开的

[永生天地]

select top 1 * from tb where users = user2 ,id<13 desc
union all
select top 1 * from tb where users = user2 ,id>13 desc

[chuifengde]

create table b_(id int, content varchar(20), users varchar(20))
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
)aaaa

union 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 行）

*/

[fireman_me]

补充一下，当查询无相关结果时

以0 补充

2.当user = user2,id=15时，当下一条没有时 ，结果为：
id content users
--------------------------------
13 XXXXX user2
0 0 0

3.当user=user2,id=5时，当上一条没有时，结果为
id content users
--------------------------------
0 0 0
13 XXXXX user2

id 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

[永生天地]

create table tb(id int, content varchar(20), users varchar(20))
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 行）

*/

[fireman_me]

[Quote=引用 7 楼 xys_777 的回复:]

SQL code

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
un……
[/Quote]

如果，上，下都有记录时，会出现4条记录。。。5楼是我想要的结果 ，不过，可以再优化下吗？

[永生天地]

[Quote=引用 9 楼 fireman_me 的回复:]
引用 7 楼 xys_777 的回复:

SQL code

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……
[/Quote]

4条，怎么会，第一个不就是上下都有的么

[fireman_me]

[Quote=引用 10 楼 xys_777 的回复:]

引用 9 楼 fireman_me 的回复:
引用 7 楼 xys_777 的回复:

SQL code

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 * fr……
[/Quote]

刚才，CSDN升级，我只能看到七楼。

[summer0208]

[Quote=引用 10 楼 xys_777 的回复:]
引用 9 楼 fireman_me 的回复:
引用 7 楼 xys_777 的回复:

SQL code

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 * fro……
[/Quote]

很不错了

[fireman_me]

下午上班，结帖

[幸运的意外]

将两部分语句用union all联接就可以了呀

[notfoundme]

顶一下