Use Oracle lead

From: http://blog.csdn.net/maqinqin/archive/2008/11/17/3320247.aspx

 

Oracle statistical analysis function lead

Syntax structure:

Lead (value_expr [, offset] [, default]) over ([query_partition_clause] order by order_by_clause)

Parameter description:

Value_expr value expression, usually a field or expression. Value_expr itself does not support analysis functions, that is, lead does not support multi-layer calls.
Offset offset, which should be a familiar mathematical concept or relative offset. The table is used to open the offset row of the current row, if offset is an integer, it indicates the nth row before the order, and if it is a negative number, it indicates the nth row after the order. If this parameter is not provided, the default value is 1.
Default default value. If not found, what value should be returned, which is a bit similar to nvl (COL, value ). If no value is set and cannot be found, null is returned.
Over can be simply translated into something... Based on
The query_partition_clause partition statement is optional for the result Set Partition statement. If not, it is all partitions.
The order_by_clause sorting statement is required, for example, order by xxx desc/ASC.

 

Example:
SQL> select * From test_value;

Mons jjr cjl cjje
----------------------------------------
200801 lzf 250 1999
200802 lzf 200 2000
200803 lzf 300 1000
200804 lzf 23 189
200805 lzf 356 456
200806 lzf 100 200
200807 lzf 600 700
200808 lzf 23 123
200809 lzf 400 500

9 rows selected

SQL>
SQL> select rownum No., Mons, cjl cjl_01,
2 lead (cjl, 1) over (order by Mons DESC) cjl_02,
3 lead (cjl, 2) over (order by Mons DESC) cjl_03,
4 lead (cjl, 3) over (order by Mons DESC) cjl_04,
5 lead (cjl, 4) over (order by Mons DESC) cjl_05,
6 lead (cjl, 5) over (order by Mons DESC) cjl_06,
7 lead (cjl, 6) over (order by Mons DESC) cjl_07,
8 Lead (cjl, 7) over (order by Mons DESC) cjl_08,
9 lead (cjl, 8) over (order by Mons DESC) cjl_09
10 from test_value

No. Mons cjl_01 cjl_02 cjl_03 cjl_04 cjl_05 cjl_06 cjl_07 cjl_08 cjl_09
--------------------------------------------------------------------------------------------------------------
9 200809 400 23 600 100 356 23 300 200 250
8 200808 23 600 100 356 23 300 200 250
7 200807 600 100 356 23 300 200 250
6 200806 100 356 23 300 200 250
5 200805 356 23 300 200 250
4 200804 23 300 200 250
3 200803 300 200 250
2 200802 200 250
1 200801 250

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/maqinqin/archive/2008/11/17/3320247.aspx