with tmp as(
select '1' id ,'aa' name ,'22' age from dual union all
select '2' id ,'bb' name ,'20' age from dual union all
select '3' id ,'CC' name ,'21' age from dual
)
select a.*,
lead(age,1) over (order by id desc) lag,
a.age - lead(age,1) over (order by id desc) lag1
from tmp a
lead函数是按id倒序排序把下一行的age记录显示在当前行的列lag,为null显示null
with tmp as(
select '1' id ,'aa' name ,'22' age from dual union all
select '2' id ,'bb' name ,'20' age from dual union all
select '3' id ,'CC' name ,'21' age from dual
)
select a.*,
lag(age,1) over (order by id desc) lag,
a.age - lag(age,1) over (order by id desc) lag1
from tmp a
lag函数是按id倒序排序把上一行的age记录显示在当前行的列lag,为null显示null
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步