oracle 分析函数lag lead

LAG is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.

For the optional offset argument, specify an integer that is greater than zero. If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, then its default is null.

{RESPECT | IGNORE} NULLS determines whether null values of value_expr are included in or eliminated from the calculation. The default is RESPECT NULLS.

You cannot nest analytic functions by using LAG or any other analytic function for value_expr. However, you can use other built-in function expressions for value_expr.

 

比如有这样一个需求，按员工号排序。显示每个员工的sal的上一个sal是多少。

SELECT empno,ename, sal, LAG(sal,1,sal)  over(ORDER BY empno)AS lastone FROM emp

结果如图

1 7369 SMITH 800.00 800
2 7499 ALLEN 1600.00 800
3 7521 WARD 1250.00 1600
4 7566 JONES 2975.00 1250
5 7654 MARTIN 1250.00 2975
6 7698 BLAKE 2850.00 1250
7 7782 CLARK 2450.00 2850
8 7788 SCOTT 3000.00 2450
9 7839 KING 5000.00 3000
10 7844 TURNER 1500.00 5000
11 7876 ADAMS 1100.00 1500
12 7900 JAMES 950.00 1100
13 7902 FORD 3000.00 950
14 7934 MILLER 1300.00 3000

 

lead则为下一个，如lag