本文共 5473 字，大约阅读时间需要 18 分钟。
Oracle 12c 之分析函数— LAST_VALUE
LAST_VALUE也是一个分析函数，它返回一个有序的值集合中的最后一个值。
可以对比FIRST_VALUE函数。
我们来看看例子：
SQL> SELECT employee_id, last_name, salary, hire_date, 2 LAST_VALUE(hire_date) 3 OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED 4 FOLLOWING) AS lv 5 FROM (SELECT * FROM employees 6 WHERE department_id = 90 7 ORDER BY hire_date);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV----------- -------------------------------------------------- ---------- -------------- -------------- 100 King 24000 17-6月 -03 13-1月 -01 101 Kochhar 17000 21-9月 -05 13-1月 -01 102 De Haan 17000 13-1月 -01 13-1月 -01已用时间: 00: 00: 00.05
看看下面的查询与上面的查询有什么不同？
SQL>SQL> SELECT employee_id, last_name, salary, hire_date, 2 LAST_VALUE(hire_date) 3 OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED 4 FOLLOWING) AS lv 5 FROM (SELECT * FROM employees 6 WHERE department_id = 90 7 ORDER BY hire_date DESC);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV----------- -------------------------------------------------- ---------- -------------- -------------- 100 King 24000 17-6月 -03 21-9月 -05 102 De Haan 17000 13-1月 -01 21-9月 -05 101 Kochhar 17000 21-9月 -05 21-9月 -05已用时间: 00: 00: 00.01SQL>
虽然 101号员工和102号员工具有相同的工资（Salary均为17000），但是在查询中，子查询中使用hire_date列进行了排序处理，第二个SQL查询语句中，按照 hire_date的降序（DESC）进行了排序，所以在查询结果中，102号员工排在了101号员工之前。
再看下面的两条SQL查询，找出不同点：
SQL>SQL> SELECT employee_id, last_name, salary, hire_date, 2 LAST_VALUE(hire_date) 3 OVER (ORDER BY salary DESC, employee_id ROWS BETWEEN UNBOUNDED PRECEDING 4 AND UNBOUNDED FOLLOWING) AS lv 5 FROM (SELECT * FROM employees 6 WHERE department_id = 90 7 ORDER BY hire_date);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV----------- -------------------------------------------------- ---------- -------------- -------------- 100 King 24000 17-6月 -03 13-1月 -01 101 Kochhar 17000 21-9月 -05 13-1月 -01 102 De Haan 17000 13-1月 -01 13-1月 -01已用时间: 00: 00: 00.01SQL>SQL> SELECT employee_id, last_name, salary, hire_date, 2 LAST_VALUE(hire_date) 3 OVER (ORDER BY salary DESC, employee_id ROWS BETWEEN UNBOUNDED PRECEDING 4 AND UNBOUNDED FOLLOWING) AS lv 5 FROM (SELECT * FROM employees 6 WHERE department_id = 90 7 ORDER BY hire_date DESC);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV----------- -------------------------------------------------- ---------- -------------- -------------- 100 King 24000 17-6月 -03 13-1月 -01 101 Kochhar 17000 21-9月 -05 13-1月 -01 102 De Haan 17000 13-1月 -01 13-1月 -01已用时间: 00: 00: 00.01SQL>
By ordering within the function by both salary and the unique key employee_id, you can ensure the same result regardless of the ordering in the subquery.
再看RANGE子句的使用：
SQL>SQL> SELECT employee_id, last_name, salary, hire_date, 2 LAST_VALUE(hire_date) 3 OVER (ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 4 UNBOUNDED FOLLOWING) AS lv 5 FROM (SELECT * FROM employees 6 WHERE department_id = 90 7 ORDER BY hire_date);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV----------- -------------------------------------------------- ---------- -------------- -------------- 100 King 24000 17-6月 -03 21-9月 -05 102 De Haan 17000 13-1月 -01 21-9月 -05 101 Kochhar 17000 21-9月 -05 21-9月 -05已用时间: 00: 00: 00.01SQL>SQL> SELECT employee_id, last_name, salary, hire_date, 2 LAST_VALUE(hire_date) 3 OVER (ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 4 UNBOUNDED FOLLOWING) AS lv 5 FROM (SELECT * FROM employees 6 WHERE department_id = 90 7 ORDER BY hire_date DESC);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV----------- -------------------------------------------------- ---------- -------------- -------------- 100 King 24000 17-6月 -03 21-9月 -05 102 De Haan 17000 13-1月 -01 21-9月 -05 101 Kochhar 17000 21-9月 -05 21-9月 -05已用时间: 00: 00: 00.00SQL>SQL>
转载地址：http://zftdi.baihongyu.com/