Oracle分析函数FIRST_VALUE、LAST_VALUE，
FIRST_VALUE、LAST_VALUE分析函数可以按照特定分组和排序取出组内首尾值，语法
FIRST_VALUE
{ (expr) [ {RESPECT | IGNORE} NULLS ]
| (expr [ {RESPECT | IGNORE} NULLS ])
}
OVER (analytic_clause)
测试下
10:48:07 SCOTT@study> SELECT EMPNO,
10:48:15 2 DEPTNO,
10:48:15 3 SAL,
10:48:15 4 FIRST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL) AS LOWEST_IN_DEPT,
10:48:15 5 FIRST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS 1 PRECEDING) AS PRECEDING_IN_DEPT,
10:48:15 6 LAST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL) AS HIGHEST_IN_DEPT,
10:48:15 7 LAST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS HIGHEST_IN_DEPT
10:48:15 8 FROM EMP;
EMPNO DEPTNO SAL LOWEST_IN_DEPT PRECEDING_IN_DEPT HIGHEST_IN_DEPT HIGHEST_IN_DEPT
---------- ---------- ---------- -------------- ----------------- --------------- ---------------
7934 10 1300 1300 1300 1300 5000
7782 10 2450 1300 1300 2450 5000
7839 10 5000 1300 2450 5000 5000
7369 20 800 800 800 800 3000
7876 20 1100 800 800 1100 3000
7566 20 2975 800 1100 2975 3000
7788 20 3000 800 2975 3000 3000
7902 20 3000 800 3000 3000 3000
7900 30 950 950 950 950 2850
7654 30 1250 950 950 1250 2850
7521 30 1250 950 1250 1250 2850
7844 30 1500 950 1250 1500 2850
7499 30 1600 950 1500 1600 2850
7698 30 2850 950 1600 2850 2850
14 rows selected.
Elapsed: 00:00:00.00
10:48:17 SCOTT@study>
LAST_VALUE的默认写法结果不符合预期，是因为默认的开窗语句"RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"，所以需要显性写出正确的开窗语句
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。