求救........急急急....

[Lcindep110]

有表如下:
id title counter
1 aaa 2
2 bbb 2
3 ccc 2
当我有ID2时,可得到ID 2的COUNTER
这时,我想根据这个ID的COUNTER得到上一条跟下一条记录.
相等时,原来顺序排序

[Lcindep110]

自己顶..

人都哪去啦.....出人命咯...!!..

[lxzm1001]

说清楚点吧

[Lcindep110]

如果记录如下
ID TITLE COUNTER
1 aaa 0
2 bbb 1
3 ccc 2
时,知道ID是2且COUNTER是1时,根据COUNTER排序就可得到上一条跟下一条记录,
但COUNTER是相同时,就不知道怎样取到

[samfeng_2003]

declare @t table
(id int,title varchar(20),counter int)

insert @t values (1,'aaa',2)
insert @t values (2,'bbb',2)
insert @t values (3,'ccc',2)

select * from @t order by case when id=2 then 0 else 1 end,id asc

id title counter
----------- -------------------- -----------
2 bbb 2
1 aaa 2
3 ccc 2

（所影响的行数为 3 行）

[samfeng_2003]

declare @t table
(id int,title varchar(20),counter int)

insert @t values (1,'aaa',2)
insert @t values (2,'bbb',2)
insert @t values (3,'ccc',2)
insert @t values (4,'ddd',2)

select * from
(
select top 1 * from @t where id<3 order by id desc
) a
[union] all /*-----------去掉[]号---*/
select * from
(
select top 1 * from @t where id>3
) b

id title counter
----------- -------------------- -----------
2 bbb 2
4 ddd 2

（所影响的行数为 2 行）

[yinwun]

create table #A ([id] int, title varchar(50), counter int)
insert into #A select 1, 'aaa', 2
insert into #A select 2, 'bbb', 2
insert into #A select 3, 'ccc', 3
insert into #A select 4, 'ccc', 4
insert into #A select 5, 'ccc', 5
insert into #A select 6, 'ccc', 6
insert into #A select 7, 'ccc', 8


if exists(select [id] from #A where [id] = 5)
Begin
select * from (
select top 1 * from #A where [id] < 5 order by [id] desc ) a
UNION ALL
select top 1 * from #A where [id] > 5
End

[Lcindep110]

但是我那个COUNTER是未知的....

[Lcindep110]

不好意思发错...