NTH_VALUE:获取排序后结果集中任意一行,而不会仅仅第一行或最后一行!
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
- SQL> SELECT NTH_VALUE(T.SNAME, 3) FROM FIRST OVER() AS 第三名,
- 2 NTH_VALUE(T.SNAME, 2) FROM LAST OVER() AS 倒数第二名,
- 3 T.*
- 4 FROM STUDENT T
- 5 ORDER BY AGE;
- 第三名 倒数第二名 SNO SNAME AGE
- ---------- ---------- ----- ---------- ----------
- KAY CHAD 6 JING 18
- KAY CHAD 4 MAGGIE 19
- KAY CHAD 8 KAY 20
- KAY CHAD 1 AARON 20
- KAY CHAD 3 DOUG 20
- KAY CHAD 9 GILLIAN 20
- KAY CHAD 7 BRIAN 21
- KAY CHAD 2 CHUCK 21
- KAY CHAD 10 CHAD 21
- KAY CHAD 5 STEVE 22
- 10 rows selected
- SQL>
NTH_VALUE :第一个参数为列名,第二个参数为窗口位移量(例如:nth_value(SNAME,2))。
from first和respect_nulls为默认值。FROM FIRST:从窗口的第一行开始寻找位移后的数据行。
FROM LAST:从窗口的第一行开始寻找位移后的数据行。
RESPECT NULLS :如果在位移行中包含空值则将会返回空值。