select * from aa01_2014 where aaa001=(select c.p from (select aaa001,lag(aaa001,1,0) over (order by aaa001) as p from aa01_2014) c where c.aaa001='8a9299ec522f54f401522f81eedc0007') ;
select * from aa01_2014 where aaa001=(select c.n from (select aaa001,lead(aaa001,1,0) over (order by aaa001) as n from aa01_2014) c where c.aaa001='8a9299ec522f54f401522f81eedc0007') ;
oracle可以使用 lead、lag 函数来查询已有记录的下一条、上一条记录。
表结构如下：
如要查询Staffno是6-1102的前一条记录
select * from staff where staff_no=(select c.p from (select staff_no,lag(staff_no,1,0) over (order by staff_no) as p from staff) c where c.staff_no='6-1102')
结果：
STAFF_NO STAFF_NAME SEX
---------- -------------------- --- -
6-1076 梁柄聪 男
1 rows selected
如要查询其后一条记录
select * from staff where staff_no=(select c.n from (select staff_no,lead(staff_no,1,0) over (order by staff_no) as n from staff) c where c.staff_no='6-1102')
结果：
STAFF_NO STAFF_NAME SEX
---------- -------------------- --- -
6-1103 余志伟 男
1 rows selected