NAME ID
-------------------- ----------
rqj 4
oqq 7
gew 2
fekj 6
agre 3
ADFD 1
5er 5
1r4 8
已选择8行。
SQL> select * from
2 (select t.*,first_value(id) over(order by name desc) first_id,
3 first_value(id) over(order by name) last_id,
4 lag(id) over(order by name desc) pre_id,
5 lead(id) over(order by name desc) next_id
6 from tbname1 t)
7 where id = 2
8 /
NAME ID FIRST_ID LAST_ID PRE_ID NEXT_ID
-------------------- ---------- ---------- ---------- ---------- ----------
gew 2 4 8 7 6
改一下
select * from
(select t.*,first_value(id) over(order by name desc) first_id,last_value(id) over(order by name desc) last_id,lag(id) over(order by name desc) pre_id,lead(id) over(order by name desc) next_id from 表 t)
where id = 2
下面语句查出id为2的纪录的前一条和后1条id以及name最大和最小对应的id
select * from
(select t.*,first_value(id) over(order by name desc) first_id,last_value(id) over(order by name desc) last_id,lag(id) over(order by name desc) pre_id,lag(id,-1) over(order by name desc) next_id from 表 t)
where id = 2