转自：http://blog.csdn.net/wh62592855/article/details/4988336
例如说吧，对DEPTNO 10中的每个员工，确定聘用他们的日期及聘用下一个员工（可能是其他部门的员工）的日期之间相差的天数。
SQL> select ename,hiredate,deptno from emp order by hiredate;
ENAME HIREDATE DEPTNO
---------- --------------- ----------
SMITH 17-DEC-80 20
ALLEN 20-FEB-81 30
WARD 22-FEB-81 30
JONES 02-APR-81 20
BLAKE 01-MAY-81 30
CLARK 09-JUN-81 10
TURNER 08-SEP-81 30
MARTIN 28-SEP-81 30
KING 17-NOV-81 10
JAMES 03-DEC-81 30
FORD 03-DEC-81 20
ENAME HIREDATE DEPTNO
---------- --------------- ----------
MILLER 23-JAN-82 10
SCOTT 19-APR-87 20
ADAMS 23-MAY-87 20
14 rows selected.
SQL> select ename,hiredate,next_hd,
2 next_hd-hiredate diff
3 from
4 (
5 select deptno,ename,hiredate,
6 lead(hiredate) over(order by hiredate) next_hd
7 from emp
8 )
9 where deptno=10;
ENAME HIREDATE NEXT_HD DIFF
---------- --------------- --------------- ----------
CLARK 09-JUN-81 08-SEP-81 91
KING 17-NOV-81 03-DEC-81 16
MILLER 23-JAN-82 19-APR-87 1912
这里的LEAD OVER非常有用，它能够访问“未来的”行（“未来的”行相对于当前行，由ORDER BY子句决定）。这种无需添加联接就能够访问当前行附近行的功能，提高了代码的可读性和有效性。在采用窗口函数时，一定要记住，它在WHERE子句之后求值，因此在该解决方案中，需要使用内联视图。如果把对DEPTNO的筛选移到内联视图，则结果会发生改变（仅考虑了DETPNO 10中的HIREDATE）。
所以下面的结果是错误的：
SQL> select ename,hiredate,next_hd,
2 next_hd-hiredate diff
3 from
4 (
5 select deptno,ename,hiredate,
6 lead(hiredate) over(order by hiredate) next_hd
7 from emp
8 where deptno=10
9 );
ENAME HIREDATE NEXT_HD DIFF
---------- --------------- --------------- ----------
CLARK 09-JUN-81 17-NOV-81 161
KING 17-NOV-81 23-JAN-82 67
MILLER 23-JAN-82
对于ORACLE的LEAD和LAG函数还需要特别注意，它们的结果中可能会有重复。在上面的例子中表EMP内不包含重复的HIREDATE，所以“看起来”似乎没有什么问题。下面我们向表中插入4个重复值来看看
SQL> insert into emp(empno,ename,deptno,hiredate)
2 values(1,'a',10,to_date('17-NOV-1981'));
1 row created.
SQL> insert into emp(empno,ename,deptno,hiredate)
2 values(2,'b',10,to_date('17-NOV-1981'));
1 row created.
SQL> insert into emp(empno,ename,deptno,hiredate)
2 values(3,'c',10,to_date('17-NOV-1981'));
1 row created.
SQL> insert into emp(empno,ename,deptno,hiredate)
2 values(4,'d',10,to_date('17-NOV-1981'));
1 row created.
SQL> select ename,hiredate
2 from emp
3 where deptno=10
4 order by 2;
ENAME HIREDATE
---------- ---------------
CLARK 09-JUN-81
b 17-NOV-81
c 17-NOV-81
a 17-NOV-81
d 17-NOV-81
KING 17-NOV-81
MILLER 23-JAN-82
7 rows selected.
现在还是用以前那个查询语句来试试
SQL> select ename,hiredate,next_hd,
2 next_hd-hiredate diff
3 from
4 (
5 select deptno,ename,hiredate,
6 lead(hiredate) over(order by hiredate) next_hd
7 from emp
8 )
9 where deptno=10;
ENAME HIREDATE NEXT_HD DIFF
---------- --------------- --------------- ----------
CLARK 09-JUN-81 08-SEP-81 91
d 17-NOV-81 17-NOV-81 0
c 17-NOV-81 17-NOV-81 0
a 17-NOV-81 17-NOV-81 0
b 17-NOV-81 17-NOV-81 0
KING 17-NOV-81 03-DEC-81 16
MILLER 23-JAN-82 19-APR-87 1912
7 rows selected.
可以看到其中有4个员工的DIFF列值都是0，这是错误的，同一天聘用的所有员工都应该跟下一个聘用其他员工的HIREDATE进行计算。
幸运的是ORACLE针对这类情况提供了一个非常简单的措施：当调用LEAD函数时，可以给LEAD传递一个参数，以便准确的指定“未来的”行（是下一行？10行之后？等等）。
select ename,hiredate,next_hd,
next_hd-hiredate diff
from
(
select deptno,ename,hiredate,
lead(hiredate,cnt-rn+1) over(order by hiredate) next_hd
from
(
select deptno,ename,hiredate,
count(*) over(partition by hiredate) cnt,
row_number() over(partition by hiredate order by empno) rn
from emp
where deptno=10
)
)