取当前记录行的后一行记录和前一行记录lead()和lag()

                                                                                              Lead() 和 lag()

 

lead(列名,n,m): 当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。 
lag(列名,n,m): 当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。

 

--测试脚本

 create table tmp_wrh_emp(enamevarchar2(100),job varchar2(100),sal number, deptno number,create_date date);

 insert into tmp_wrh_emp(ename ,job ,sal , deptno ,create_date )

 select 'ALLEN','dev',1000,10,sysdate from dual

 union

 select 'BOB','dev',2000,10,sysdate from dual

 union

 select 'CANDY','dev',3000,10,sysdate from dual

 union

 select 'DOIL','dev',4000,10,sysdate from dual

 union

 select 'EPSON','dev',5000,10,sysdate from dual;

 

select e.ename,

      e.job,

      e.sal,

      e.deptno,

      lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal,

      lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal,

      nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal,

          0) diff_lead_sal,

      nvl(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0)diff_lag_sal

 from tmp_wrh_emp e;

 

执行结果：

	ENAME	JOB	SAL	DEPTNO	LEAD_SAL	LAG_SAL	DIFF_LEAD_SAL	DIFF_LAG_SAL
1	ALLEN	dev	1000	10	2000	0	1000	0
2	BOB	dev	2000	10	3000	1000	1000	1000
3	CANDY	dev	3000	10	4000	2000	1000	1000
4	DOIL	dev	4000	10	5000	3000	1000	1000
5	EPSON	dev	5000	10	0	4000	0	1000