一个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