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
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.pxdrop 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 行)
*/
(
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,IDselect 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.codedrop 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)
*/
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
--我来个更简单的
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 *,uid=identity(int,1,1) into #t from tb
goselect CODE,ID ,previous_ID=(select top 1 id from #t where a.code=code and uid<a.uid),
next_ID=(select top 1 id from #t where a.code=code and uid>a.uid)
from #t aCODE ID previous_ID next_ID
---------- ----------- ----------- -----------
AAA 1 NULL 3
AAA 3 1 4
AAA 4 1 NULL
BBB 2 NULL 3
BBB 3 2 NULL(所影响的行数为 5 行)
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 #
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 *,uid=identity(int,1,1) into #t from tb
goselect CODE,ID ,previous_ID=(select top 1 id from #t where a.code=code and uid<a.uid order by uid desc),
next_ID=(select top 1 id from #t where a.code=code and uid>a.uid order by uid )
from #t aCODE 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 行)