Summary_Oracle lag()/lead() over()分析函数

--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.*,
       lead(age,1) over (order by id desc) lag,
       a.age - lead(age,1) over (order by id desc) lag1
from tmp a;

注意：如果不使用lead()over()函数实现上图查询结果，则需要下面的SQL语句：

        SELECT T1.*,T2.AGE AS LAG,T1.AGE-T2.AGE  AS LAG1 FROM (
        SELECT a.*,ROWNUM AS R1 FROM (SELECT ID,NAME,AGE FROM tmp GROUP BY ID,NAME,AGE ORDER BY ID DESC) a
        ) T1 LEFT JOIN (
        SELECT b.*,ROWNUM-1 AS R2 FROM (SELECT ID,NAME,AGE FROM tmp GROUP BY ID,NAME,AGE ORDER BY ID DESC) b
        ) T2 ON T1.R1=T2.R2 ORDER BY T1.ID DESC;

--lag函数是按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;

注意：如果不使用lead()over()函数实现上图查询结果，则需要下面的SQL语句：

        SELECT T1.*,T2.AGE AS LAG,T1.AGE-T2.AGE  AS LAG1 FROM (
        SELECT a.*,ROWNUM-1 AS R1 FROM (SELECT ID,NAME,AGE FROM tmp GROUP BY ID,NAME,AGE ORDER BY ID DESC) a
        ) T1 LEFT JOIN (
        SELECT b.*,ROWNUM AS R2 FROM (SELECT ID,NAME,AGE FROM tmp GROUP BY ID,NAME,AGE ORDER BY ID DESC) b
        ) T2 ON T1.R1=T2.R2 ORDER BY T1.ID DESC;