轉(zhuǎn)自：http://blog.csdn.net/wh62592855/article/details/4988336
例如說吧，對DEPTNO 10中的每個員工，確定聘用他們的日期及聘用下一個員工（可能是其他部門的員工）的日期之間相差的天數(shù)。
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非常有用，它能夠訪問“未來的”行（“未來的”行相對于當(dāng)前行，由ORDER BY子句決定）。這種無需添加聯(lián)接就能夠訪問當(dāng)前行附近行的功能，提高了代碼的可讀性和有效性。在采用窗口函數(shù)時，一定要記住，它在WHERE子句之后求值，因此在該解決方案中，需要使用內(nèi)聯(lián)視圖。如果把對DEPTNO的篩選移到內(nèi)聯(lián)視圖，則結(jié)果會發(fā)生改變（僅考慮了DETPNO 10中的HIREDATE）。
所以下面的結(jié)果是錯誤的：
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函數(shù)還需要特別注意，它們的結(jié)果中可能會有重復(fù)。在上面的例子中表EMP內(nèi)不包含重復(fù)的HIREDATE，所以“看起來”似乎沒有什么問題。下面我們向表中插入4個重復(fù)值來看看
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.
現(xiàn)在還是用以前那個查詢語句來試試
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，這是錯誤的，同一天聘用的所有員工都應(yīng)該跟下一個聘用其他員工的HIREDATE進(jìn)行計算。
幸運(yùn)的是ORACLE針對這類情況提供了一個非常簡單的措施：當(dāng)調(diào)用LEAD函數(shù)時，可以給LEAD傳遞一個參數(shù)，以便準(zhǔn)確的指定“未來的”行（是下一行？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
)
)