Query the previous and subsequent records in Oracle based on the current record

SELECT * from aa01_2014 where aaa001= (select C.P from (select Aaa001,lag (aaa001,1,0) over (order by aaa001) as p from aa0 1_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 AA 01_2014) c where c.aaa001= ' 8a9299ec522f54f401522f81eedc0007 ');

Oracle can use the lead, lag function to query the next, previous record of an existing record.

The table structure is as follows:

To query Staffno is the previous record of 6-1102

SELECT * from the 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 ')

Results:

Staff_no Staff_name SEX

---------- -------------------- --- -

6-1076     Liang               men             &NBS P                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp                                    

1 rows selected

To query for a subsequent record

SELECT * from the staff where staff_no= (select C.N from (select Staff_no,lead (staff_no,1,0) over (order by staff_no) as N fro M staff) c where c.staff_no= ' 6-1102 ')

Results:

Staff_no Staff_name SEX

---------- -------------------- --- -

6-1103 Yu Zhiwei Male

1 rows selected

Query the previous and subsequent records in Oracle based on the current record