Oracle 12c 之分析函数— FIRST_VALUE
FIRST_VALUE是一个分析函数,它返回一个有序的值集合中的第一个值。如果集合中的第一个值为NULL,则函数将返回NULL,除非指定忽略NULL。
下面我们来看看如何使用这个分析函数:
以Oracle 12c Sample中的HR用户为例,查找部门编号为90部门的员工的最低工资:
SELECT employee_id, last_name, salary, hire_date, FIRST_VALUE(last_name) OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS fv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date);
SQL> set linesize 200;
SQL> set pagesize 999;
SQL> set timing on;
SQL> SELECT employee_id, last_name, salary, hire_date,
2 FIRST_VALUE(last_name)
3 OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS fv
4 FROM (SELECT * FROM employees
5 WHERE department_id = 90
6 ORDER BY hire_date);
EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE FV
----------- -------------------------------------------------- ---------- -------------- --------------------------------------------------
102 De Haan 17000 13-1月 -01 De Haan
101 Kochhar 17000 21-9月 -05 De Haan
100 King