Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现,不过使用LAG和LEAD有更高的效率。以下是LAG和LEAD的例子:51Testing软件测试网A$[ k|j^{-am
SQL> select year,region,profit ,lag (profit,1) over (order by year)
/PS$Oe0j9S0
L|\&LE+H0 2 as last_year_exp from test;51Testing软件测试网@`)u|)lpG-\3VP
51Testing软件测试网EQ%A'^G%UP
YEAR REGION PROFIT LAST_YEAR_EXP51Testing软件测试网8}Y-? D
b
---- ------- ---------- -------------
-tt9I?#I:eEKT1Q:?j02003 West 88
)T(AEHM.R.m}v5T3Px.?02003 West 88 88
h b!c!E)fK9C02003 Central 101 88
&RQ,nD'bo02003 Central 100 10151Testing软件测试网`!W&Uh4D:H0oo"IN
2003 East 102 10051Testing软件测试网,w^9l9u/{ h g"M9_C
2004 West 77 10251Testing软件测试网9DF&T"noH Oc(T(Q6YT_
2004 East 103 7751Testing软件测试网d7tN$R K
2004 West 89 103
FQ b7C3d[t0
&t
Kfr!MXb
y_0SQL> select year,region,profit ,lead (profit,1) over (order by year)51Testing软件测试网
e*s"P1O [)S
vO
51Testing软件测试网#cMi4T3dB S$E
2 as next_year_exp from test;
8O r3io"w%A_'j0 51Testing软件测试网UF|hGsG1]
YEAR REGION PROFIT NEXT_YEAR_EXP51Testing软件测试网8Dyp5K$D9~7|$})D:vC
---- ------- ---------- -------------
`\M)Cs-a,I
Jma02003 West 88 8851Testing软件测试网[ c@Qd)iOmz
2003 West 88 10151Testing软件测试网0o(| V2M`.PGO
2003 Central 101 10051Testing软件测试网-V-{'x'je&CV
2003 Central 100 102
(xm9@)Pw.\!E02003 East 102 77
5uIn4kUr02004 West 77 103
w:i
Bv&F