可以在不进行表连接的情况下访问当前行之后的行值。
lead(arg1,arg2) arg1表示列名,arg2表示行偏移量,默认为1。 当找不到值时返回null 。
例:查询emp表中每个员工及在他之后被雇佣的员工的名字。
SQL> select empno,hiredate,ename,lead(ename)over(order by hiredate) from emp order by hiredate ;
EMPNO HIREDATE ENAME LEAD(ENAME)OVER(ORDERBYHIREDAT
----- ----------- ---------- ------------------------------
7369 1980/12/17 SMITH ALLEN
7499 1981/2/20 ALLEN WARD
7521 1981/2/22 WARD JONES
7566 1981/4/2 JONES BLAKE
7698 1981/5/1 BLAKE CLARK
7782 1981/6/9 CLARK TURNER
7844 1981/9/8 TURNER MARTIN
7654 1981/9/28 MARTIN KING
7839 1981/11/17 KING JAMES
7900 1981/12/3 JAMES FORD
7902 1981/12/3 FORD MILLER
7934 1982/1/23 MILLER SCOTT
7788 1987/4/19 SCOTT ADAMS
7876 1987/5/23 ADAMS
因为最后一行的员工是最后被雇佣的,所以他的下面没有其他员工了,lead(ename)返回null 。
SQL> select empno,hiredate,ename,lead(ename,2)over(order by hiredate) from emp order by hiredate ;
EMPNO HIREDATE ENAME LEAD(ENAME,2)OVER(ORDERBYHIRED
----- ----------- ---------- ------------------------------
7369 1980/12/17 SMITH WARD
7499 1981/2/20 ALLEN JONES
7521 1981/2/22 WARD BLAKE
7566 1981/4/2 JONES CLARK
7698 1981/5/1 BLAKE TURNER
7782 1981/6/9 CLARK MARTIN
7844 1981/9/8 TURNER KING
7654 1981/9/28 MARTIN JAMES
7839 1981/11/17 KING FORD
7900 1981/12/3 JAMES MILLER
7902 1981/12/3 FORD SCOTT
7934 1982/1/23 MILLER ADAMS
7788 1987/4/19 SCOTT
7876 1987/5/23 ADAMS