Oracle查询当前某条数据的前一行数据与后一行数据

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