oracle分析函数 first,oracle 分析函數 FIRST_VALUE、LAST_VALUE

用SCOTT/TIGER登錄。

FIRST_VALUE、LAST_VALUE是兩個分析函數。返回結果集中排在第一位和最后一位的值。

使用FIRST_VALUE：

SELECT DEPTNO, JOB, SUM(SAL), FIRST_VALUE(SUM(SAL)) OVER (PARTITION BY DEPTNO ORDER BY SUM(SAL))

FROM EMP GROUP BY DEPTNO, JOB ORDER BY DEPTNO, JOB;

結果：

使用LAST_VALUE：

SELECT DEPTNO, JOB, SUM(SAL), LAST_VALUE(SUM(SAL)) OVER (PARTITION BY DEPTNO ORDER BY SUM(SAL) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

FROM EMP GROUP BY DEPTNO, JOB ORDER BY DEPTNO, JOB;

結果：

對於LAST_VALUE，要加

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

否則，SQL如：

SELECT DEPTNO, JOB, SUM(SAL), LAST_VALUE(SUM(SAL)) OVER (PARTITION BY DEPTNO ORDER BY SUM(SAL))

FROM EMP GROUP BY DEPTNO, JOB ORDER BY DEPTNO, JOB;

結果：

就不對了。