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

最新推荐文章于 2024-03-14 10:25:47 发布

selfsupport

最新推荐文章于 2024-03-14 10:25:47 发布

阅读量4.6k

点赞数

文章标签： oracle date

本文链接：https://blog.csdn.net/selfsupport/article/details/3581322

版权

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