1.Oracle函数中的 FIRST_VALUE 和 LAST_VALUE
LAST_VALUE分析函数的简单用法
(1)在TEST表中添加一列,标识每一个数据分区中薪水最高的人名。
select ID, name, salary, LAST_VALUE(name) OVER (partition by ID order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from test order by ID, name;
ID NAME SALARY HIGHEST_SAL_NAME
--- ---------- -------- ----------------
1 Tom 120.00 Tom
2 Ellen 240.00 Ellen
2 Joe 80.00 Ellen
3 Andy 300.00 Erick
3 Erick 1300.00 Erick
3 Hou 40.00 Erick
3 Kary 500.00 Erick
3 Mary 200.00 Erick
3 Secooler 800.00 Erick
在TEST表中添加一列,标识每一个数据分区中薪水最高的薪水值。
select ID, name, salary, LAST_VALUE(SALARY) OVER (partition by ID order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from test order by ID, name;
ID NAME SALARY HIGHEST_SAL_NAME
--- ---------- -------- ----------------
1 Tom 120.00 120
2 Ellen 240.00 240
2 Joe 80.00 240
3 Andy 300.00 1300
3 Erick 1300.00 1300
3 Hou 40.00 1300
3 Kary 500.00 1300
3 Mary 200.00 1300
3 Secooler 800.00 1300
与之相对应的是FIRST_VALUE函数
select ID, name, salary, FIRST_VALUE(name) OVER (partition by ID order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from test order by ID, name;
ID NAME SALARY HIGHEST_SAL_NAME
--- ---------- -------- ----------------
1 Tom 120.00 Tom
2 Ellen 240.00 Joe
2 Joe 80.00 Joe
3 Andy 300.00 Hou
3 Erick 1300.00 Hou
3 Hou 40.00 Hou
3 Kary 500.00 Hou
3 Mary 200.00 Hou
3 Secooler 800.00 Hou
select ID, name, salary, FIRST_VALUE(SALARY) OVER (partition by ID order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from test order by ID, name;
ID NAME SALARY HIGHEST_SAL_NAME
--- ---------- -------- ----------------
1 Tom 120.00 120
2 Ellen 240.00 80
2 Joe 80.00 80
3 Andy 300.00 40
3 Erick 1300.00 40
3 Hou 40.00 40
3 Kary 500.00 40
3 Mary 200.00 40
3 Secooler 800.00 40
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
来源于:http://blog.csdn.net/tanzuai/article/details/42387341