求一条简单的查询语句

[kong_d_f]

假如在oracle的某表中有几千条数据,怎么才能高效率的查询到某ID的上一ID或下一ID?

[xb_feng]

SELECT * FROM table
WHERE id<:id
AND ROWNUM=1
ORDER BY id DESC

SELECT * FROM table
WHERE id>:id
AND ROWNUM=1
ORDER BY id

[kingkingkingking]

select lag(id,1) over(order by id) "id-1",
id,
lead(id,1) over(order by id) "id+1"
from tab

[rickyelf]

就楼上的方法了

[tianyacao007]

select
nex.id, nex.nextid,pri.priorid
from
(
select
t1.id id,
lead(t1.id,1) over(order by t1.id) nextid
from
item t1) nex,
(
select
t2.id id,
lag(t2.id,1) over(order by t2.id) priorid
from
item t2) pri,
where
nex.id = pri.id
and nex.id = 所需ID

[weihouyanli]

UP

[tgm78]

select * from table where id exist ('id+1','id-1')

[kong_d_f]

ID是混乱的,不是顺序的1,2,3,4,5....排下来的,也许是1,3,4,6,8,12,16....这个下来的

[tgm78]

select r,e.* from (select rownum r,last_name,salary from (
select last_name,salary from employees order by nvl(salary,0) desc)) e
where r between 6 and 8;