Oracle lead 和 lga 两个函数的用途和用法

lead : 返回下一条记录hire_date的值。
SELECT last_name, hire_date,
LEAD (hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"
FROM employees
WHERE department_id = 30;

LAST_NAME HIRE_DATE NextHired
------------------------- --------- ---------
Raphaely 07-DEC-94 18-MAY-95
Khoo 18-MAY-95 24-JUL-97
Tobias 24-JUL-97 24-DEC-97
Baida 24-DEC-97 15-NOV-98
Himuro 15-NOV-98 10-AUG-99
Colmenares 10-AUG-99

LAG : 返回前一条记录hire_date的值（没有的话，值为0）。
SELECT last_name, hire_date, salary,
LAG (salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK';


LAST_NAME HIRE_DATE SALARY PREV_SAL
------------------------- --------- ---------- ----------
Khoo 18-MAY-95 3100 0
Tobias 24-JUL-97 2800 3100
Baida 24-DEC-97 2900 2800
Himuro 15-NOV-98 2600 2900
Colmenares 10-AUG-99 2500 2600

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/selfsupport/archive/2008/12/22/3581322.aspx