Oracle小知识点

最新推荐文章于 2021-12-08 22:17:39 发布

原创最新推荐文章于 2021-12-08 22:17:39 发布 · 330 阅读

0 ·

CC 4.0 BY-SA版权

本文通过实例展示了Oracle数据库中LAST_VALUE和FIRST_VALUE函数的应用，这两种函数常用于获取每个数据分区中的最大值和最小值。文章通过具体的SQL查询演示了如何在不同条件下选取每个分区内最高或最低的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.youkuaiyun.com/tanzuai/article/details/42387341