表的结构如下:
STAFF_NO STAFF_NAME SEX
--------- ----------- ----
001 张三 男
002 李四 男
003 王五 男
如要查询Staffno是002的前一条记录
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='002')
结果:
STAFF_NO STAFF_NAME SEX
--------- ----------- ----
001 张三 男
如要查询其后一条记录
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='002')
结果:
STAFF_NO STAFF_NAME SEX
--------- ----------- ----
003 王五 男
查询第一条
select * from (select s.*,row_number()over(order by staff_no ) rn from staff s) where rn=1 ;
查询最后一条
select * from (select s.*,row_number()over(order by staff_no desc ) rn from staff s) where rn=1 ;
STAFF_NO STAFF_NAME SEX
--------- ----------- ----
001 张三 男
002 李四 男
003 王五 男
如要查询Staffno是002的前一条记录
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='002')
结果:
STAFF_NO STAFF_NAME SEX
--------- ----------- ----
001 张三 男
如要查询其后一条记录
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='002')
结果:
STAFF_NO STAFF_NAME SEX
--------- ----------- ----
003 王五 男
查询第一条
select * from (select s.*,row_number()over(order by staff_no ) rn from staff s) where rn=1 ;
查询最后一条
select * from (select s.*,row_number()over(order by staff_no desc ) rn from staff s) where rn=1 ;