上一个或下一个ID的问题

[FlashK]

[code=MSIL]create table tb1(id int,sortName varchar(10),field1 varchar(20))
insert into tb1 select 1,'D','aaaa'
insert into tb1 select 2,'C','adsfasd'
insert into tb1 select 3,'A','asdfasd'
insert into tb1 select 4,'B','adsfasd'[/code]

我的问题是，能不能有一个存储过程，两个参数（SQL,ID），可以得到某个记录的前一个ID和后一个ID
如：
1、
Sql = Select * From tb1 Order by id
ID= 2
得到 上一个为1 下一个为3
2、Sql = Select * From tb1 Order by SortName
ID= 2
得到 上一个为4 下一个为1

[hery2002]

那就根据你排序那一列来判断了,

[SinGooCMS]

不用ID的话.任何都可以.就是
上一条,小于当前字段的最大值
下一条,大于当前字段的最小值

[hery2002]

--下回问问题说清楚先,:)

declare @tb1 table (id int,sortName varchar(10),field1 varchar(20))
insert into @tb1 select 3,'A','asdfasd'
insert into @tb1 select 4,'B','adsfasd'
insert into @tb1 select 2,'C','adsfasd'
insert into @tb1 select 1,'D','aaaa'


select isnull((select top 1 id from @tb1 where sortName < t.sortName order by sortName desc),t.id) as prevId,
       t.id,
       isnull((select top 1 id from @tb1 where sortName > t.sortName order by sortName asc),t.id) as NextId
from @tb1 t
/*
prevId      id          NextId
----------- ----------- -----------
3           3           4
3           4           2
4           2           1
2           1           1
*/

[pt1314917]

create table tb1(id int,sortName varchar(10),field1 varchar(20))
insert into tb1 select 1,'D','aaaa'
insert into tb1 select 2,'C','adsfasd'
insert into tb1 select 3,'A','asdfasd'
insert into tb1 select 4,'B','adsfasd'
insert into tb1 select 5,'C','adsfasd'
insert into tb1 select 10,'A','asdfasd'
insert into tb1 select 12,'B','adsfasd'



declare @id int
declare @ids varchar(8000)
select @ids=isnull(@ids+',','')+ltrim(id) from tb1
set @id=5
select 
previd=case when charindex(','+ltrim(@id)+',',','+@ids+',')=1 then left(reverse(@ids),charindex(',',reverse(@ids))-1)
else reverse(left(reverse(left(@ids,charindex(','+ltrim(@id)+',',','+@ids+',')-2))+',',charindex(',',reverse(left(@ids,charindex(','+ltrim(@id)+',',','+@ids+',')-2))+',')-1))  end,
nextid=case when charindex(','+ltrim(@id)+',',','+@ids+',')+len(@id)-1=len(@ids) then left(@ids,charindex(',',@ids)-1)
else substring(substring(@ids,charindex(','+ltrim(@id)+',',','+@ids+',')+len(@id)+1,len(@ids))+',',0,charindex(',',substring(@ids,charindex(','+ltrim(@id)+',',','+@ids+',')+len(@id)+1,len(@ids))+',')) end

[pt1314917]

上面的代码我测试过了。符合楼主要求。可以让id=12或者等于1都可以。。

[SinGooCMS]

select top 1 * from (select * from t1 where [name]<'myname') a order by [name] desc
select top 1 * from (select * from t1 where [name]>'myname') a order by [name] desc

[viptiger]

[Quote]你这是已ID来排序的，如果不按照ID排序呢，如我给的第二个例子[/Quote]

declare @tmpTable table([id] int,num int identity)
declare @tmpnum int

insert @tmpTable([id])
Select [id] From tb1 Order by SortName

select @tmpnum  = num 
from @tmpTable
where [id] = 2

select max([id])
from  @tmpTable
where num < @tmpnum  

select min([id])
from  @tmpTable
where num > @tmpnum 

insert @tmpTable([id])
Select [id] From tb1 Order by SortName

改成exec/sp_executesql生成代码就可以了

[ojuju10]

create table tb1(id int,sortName varchar(10),field1 varchar(20))
insert into tb1 select 1,'D','aaaa'
insert into tb1 select 2,'C','adsfasd'
insert into tb1 select 3,'A','asdfasd'
insert into tb1 select 4,'B','adsfasd'


select maxid=(select top 1 id  from tb1 where id>a.id),id,
minid=(select top 1 id  from tb1 where id<a.id order by id desc) from tb1 a

[hery2002]

declare @tb1 table (id int,sortName varchar(10),field1 varchar(20))
insert into @tb1 select 1,'D','aaaa'
insert into @tb1 select 2,'C','adsfasd'
insert into @tb1 select 3,'A','asdfasd'
insert into @tb1 select 4,'B','adsfasd'

select isnull((select max(id) from @tb1 where id < t.id),t.id) as prevId,
       t.id,
       isnull((select min(id) from @tb1 where id > t.id),t.id) as NextId
from @tb1 t
/*
prevId      id          NextId
----------- ----------- -----------
1           1           2
1           2           3
2           3           4
3           4           4
*/

[viptiger]

declare @tmpTable table([id] int,num int identity)
declare @tmpnum int

insert @tmpTable([id])
Select [id] From tb1 Order by SortName

select @tmpnum  = num 
from @tmpTable
where [id] = 2

select max([id])
from  @tmpTable
where num < @tmpnum  

select min([id])
from  @tmpTable
where num > @tmpnum 

[FlashK]

你这是已ID来排序的，如果不按照ID排序呢，如我给的第二个例子。

[FlashK]

如果需要排序的内容是汉字呢？
Sql = Select * From tb1 Order by SortName
结果：
3,'A','asdfasd'
4,'B','adsfasd'
2,'C','adsfasd'
1,'D','aaaa'


我需要的结果是 前4 后1