DECLARE @T table 
(
code varchar(20),
ID INT
)
INSERT INTO @t
SELECT 'AAA',1 UNION ALL
SELECT 'AAA',3 UNION ALL 
SELECT 'AAA',4 UNION ALL 
SELECT 'BBB',2 UNION ALL 
SELECT 'BBB',3 

SELECT *,
  P_ID = (SELECT MAX(ID) FROM @t WHERE code = A.code AND ID<A.ID),
  N_ID = (SELECT MIN(ID) FROM @t WHERE code = A.code AND ID>A.ID) FROM @t A

------解决方案--------------------
SQL code
create table tb(CODE varchar(10),ID int)
insert into tb values('AAA',         1 )
insert into tb values('AAA',         3 )
insert into tb values('AAA',         4 )
insert into tb values('BBB',         2 )
insert into tb values('BBB',         3 ) 
go
select m.code,m.id,n.previous_id,m.next_id from
(
select t1.*,t2.id next_ID  from
(select px=(select count(1) from tb where CODE=a.CODE and id<a.id)+1 , * from tb a) t1 
left join
(select px=(select count(1) from tb where CODE=a.CODE and id<a.id)+1 , * from tb a) t2 
on t1.code = t2.code and t1.px = t2.px - 1
) m,
(
select t1.px,t1.code,t2.id previous_ID from 
(select px=(select count(1) from tb where CODE=a.CODE and id<a.id)+1 , * from tb a) t1 
left join
(select px=(select count(1) from tb where CODE=a.CODE and id<a.id)+1 , * from tb a) t2 
on t1.code = t2.code and t1.px - 1 = t2.px 
) n
where m.code = n.code and m.px = n.px

drop table tb

/*
code       id          previous_id next_id     
---------- ----------- ----------- ----------- 
AAA        1           NULL        3
AAA        3           1           4
AAA        4           3           NULL
BBB        2           NULL        3
BBB        3           2           NULL

（所影响的行数为 5 行）
*/

------解决方案--------------------
SQL code
DECLARE @T table 
(
code varchar(20),
ID INT
)
INSERT INTO @t
SELECT 'AAA',1 UNION ALL
SELECT 'AAA',3 UNION ALL 
SELECT 'AAA',4 UNION ALL 
SELECT 'BBB',2 UNION ALL 
SELECT 'BBB',3 

select *, nId=identity(int,1,1) into #T from @t order by code,ID

select T.code, T.ID, Tper.ID, Tnex.ID
from #T T left join #T Tper on T.nId=Tper.nId+1 and T.code=Tper.code
    left join #T Tnex on T.nId=Tnex.nId-1 and T.code=Tnex.code

drop table #T

/*
code                 ID          ID          ID
-------------------- ----------- ----------- -----------
AAA                  1           NULL        3
AAA                  3           1           4
AAA                  4           3           NULL
BBB                  2           NULL        3
BBB                  3           2           NULL

(5 row(s) affected)
*/

------解决方案--------------------
SQL code

create table tb(CODE varchar(10),ID int)
insert into tb values('AAA',         1 )
insert into tb values('AAA',         3 )
insert into tb values('AAA',         4 )
insert into tb values('BBB',         2 )
insert into tb values('BBB',         3 ) 

借用临时表方法：
select * into #temp from (select bh=(select count(1) from tb where code=a.code and  id<a.id)+1,* from tb a)b
select code,id,previous_ID=(select id from #temp where code=a.code and bh=a.bh-1),
next_ID=(select id from #temp where code=a.code and bh=a.bh+1)
from #temp a

------解决方案--------------------
SQL code