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 |