一个复杂的SQL语句，请高手帮忙。解决方法

一个复杂的SQL语句，请高手帮忙。
一个table，2个PK，

===================
CODE ID
AAA 1
AAA 3
AAA 4
BBB 2
BBB 3
... ...
====================

要得到的结果是这样：

===================
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
..........
==================== 

就是每列要显示：当前ID，前1个ID，后1个ID



[解决办法]

SQL code

DECLARE @T table (code varchar(20),ID INT)INSERT INTO @tSELECT 'AAA',1 UNION ALLSELECT '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 ) goselect 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 ) nwhere m.code = n.code and m.px = n.pxdrop table tb/*code       id          previous_id next_id     ---------- ----------- ----------- ----------- AAA        1           NULL        3AAA        3           1           4AAA        4           3           NULLBBB        2           NULL        3BBB        3           2           NULL（所影响的行数为 5 行）*/
[解决办法]
SQL code
DECLARE @T table (code varchar(20),ID INT)INSERT INTO @tSELECT 'AAA',1 UNION ALLSELECT '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,IDselect T.code, T.ID, Tper.ID, Tnex.IDfrom #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.codedrop table #T/*code                 ID          ID          ID-------------------- ----------- ----------- -----------AAA                  1           NULL        3AAA                  3           1           4AAA                  4           3           NULLBBB                  2           NULL        3BBB                  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)bselect 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 
 
--我来个更简单的t
[解决办法]

create table #(code varchar(10),id int)
insert into # select 'AAA',1
insert into # select 'AAA',3
insert into # select 'AAA',4
insert into # select 'BBB',2
insert into # select 'BBB',3
select *,(select max(id) from # where a.code=code and id<a.id) as previous_ID ,
(select min(id) from # where a.code=code and id>a.id)  as  next_ID  from # a

/*
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 行受影响)

*/
drop table #