34,576
社区成员
发帖
与我相关
我的任务
分享
create table #order(
order_date date null,
order_id varchar(100) null,
ID int null
)
insert into #order
select '2013-1-1','P468899','1' union
select '2013-1-2','P654882','4' union
select '2013-1-3','P497841','7' union
select '2013-1-3','P648818','10' union
select '2013-1-4','P188618','15' union
select '2013-1-4','P812545','18' union
select '2013-1-4','P983115','22' union
select '2013-1-5','P840214','28' union
select '2013-1-6','P4655552','33'
select * from #order where ID in(
select top 1 ID from #order where ID>10 order by ID desc
union select top 1 ID from #order where ID<10 order by ID
)
select * from (select top 1 * from tb where ID<10 order by ID DESC) tt
union all
select top 1 * from tb where ID>10
select '2013-1-1' 日期, 'P468899' 单号, 1 ID into #t union all
select '2013-1-2', 'P654882', 4 union all
select '2013-1-3', 'P497841', 7 union all
select '2013-1-3', 'P648818', 10 union all
select '2013-1-4', 'P188618', 15 union all
select '2013-1-4', 'P812545', 18 union all
select '2013-1-4', 'P983115', 22 union all
select '2013-1-5', 'P840214', 28 union all
select '2013-1-6', 'P4655552', 33
select a.*,t.nextid,t.preid
from
#t a
join
(
select 10 id, (select min(id) from #t where id>10) nextid ,(select max(id) from #t where id<10) preid
) t
on a.ID=t.id
再修改了一下
create table tb(日期 datetime, 单号 varchar(20), ID int)
insert into tb
select '2013-1-1', 'P468899', 1 union all
select '2013-1-2', 'P654882', 4 union all
select '2013-1-3', 'P497841', 7 union all
select '2013-1-3', 'P648818', 10 union all
select '2013-1-4', 'P688618', 15 union all --改了一下单号
select '2013-1-4', 'P812545', 18 union all
select '2013-1-4', 'P983115', 22 union all
select '2013-1-5', 'P840214', 28 union all
select '2013-1-6', 'P4655552', 33
go
select t1.日期,t1.单号,t1.id,
t2.id as prev_id,
t3.id as next_id
from tb t1
outer apply (select Max(id) id from tb t2 where t1.日期 >= t2.日期 and t1.单号 > t2.单号)t2
outer apply (select min(id) id from tb t3 where t3.日期 >= t1.日期 and t3.单号 > t1.单号)t3
where t1.id = 10
/*
日期 单号 id prev_id next_id
2013-01-03 00:00:00.000 P648818 10 7 18
*/
select '2013-1-1' 日期, 'P468899' 单号, 1 ID into #t union all
select '2013-1-2', 'P654882', 4 union all
select '2013-1-3', 'P497841', 7 union all
select '2013-1-3', 'P648818', 10 union all
select '2013-1-4', 'P188618', 15 union all
select '2013-1-4', 'P812545', 18 union all
select '2013-1-4', 'P983115', 22 union all
select '2013-1-5', 'P840214', 28 union all
select '2013-1-6', 'P4655552', 33
select *
from
(
select 10 id, (select min(id) from #t where id>10) nextid ,(select max(id) from #t where id<10) preid
) t
id为自增长主键的话,可以走索引,应该挺快的