Oracle - LEAD Function

最近在做 Project 時遇到資料是要 Row 的資料做相減的話，故如何使用 SQL 要如何達到此功能呢？在 Oracle 中有提供 LEAD Function，可以達到此功能.

Syntax

>──LEAD (value_expr─┬─────────┬─┬──────────┬─)───────────>

                    └─,offset─┘ └─,default─┘

>──OVER (─┬────────────────────────┬─order_by_clause)───><

          └─query_partition_clause─┘

Example

The following example provides, for each employee in the emp table, the hiredate of the employee hired just after:

SELECT ename, hiredate,

   LEAD(hiredate, 1) OVER (ORDER BY hiredate) AS "NextHired"

   FROM emp;

ENAME      HIREDATE  NextHired

---------- --------- ---------

SMITH      17-DEC-80 20-FEB-81

ALLEN      20-FEB-81 22-FEB-81

WARD       22-FEB-81 02-APR-81

JONES      02-APR-81 01-MAY-81

BLAKE      01-MAY-81 09-JUN-81

CLARK      09-JUN-81 08-SEP-81

TURNER     08-SEP-81 28-SEP-81

MARTIN     28-SEP-81 17-NOV-81

KING       17-NOV-81 03-DEC-81

JAMES      03-DEC-81 03-DEC-81

FORD       03-DEC-81 23-JAN-82

MILLER     23-JAN-82 19-APR-87

SCOTT      19-APR-87 23-MAY-87