oracle 查询高级用法,Oracle 高级查询之二分析函数部分--first_value,last_value

最新推荐文章于 2023-07-24 16:37:18 发布

weixin_39844590

最新推荐文章于 2023-07-24 16:37:18 发布

阅读量166

点赞数

文章标签： oracle 查询高级用法

本文详细介绍了Oracle数据库中LAST_VALUE和FIRST_VALUE分析函数的用法，通过实例展示了如何在数据分区内获取每个分区的最高薪水人员姓名和薪水。通过对比分析了ROWS BETWEEN UNBOUNDEDPRECEDING AND UNBOUNDEDFOLLOWING子句在函数应用中的重要性。

摘要由CSDN通过智能技术生成

ORACLE函数：LAST_VALUE,FIRST_VALUE的用法：

1、初始化原始数据：

create table test (id number(2), name varchar2(10), salary number(6,2));

insert into test values (1,'Tom',120);

insert into test values (2,'Ellen',240);

insert into test values (2,'Joe',80);

insert into test values (3,'Andy',300);

insert into test values (3,'Kary',500);

insert into test values (3,'Erick',1300);

insert into test values (3,'Hou',40);

insert into test values (3,'Mary',200);

insert into test values (3,'Secooler',800);

commit;

select * from test order by ID,name;

ID NAME SALARY

--- ---------- --------

1 Tom 120.00

2 Ellen 240.00

2 Joe 80.00

3 Andy 300.00

3 Erick 1300.00

3 Hou 40.00

3 Kary 500.00

3 Mary 200.00

3 Secooler 800.00

2、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

注意其中“ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”的使用，若省略效果如下。

select ID, name, salary, LAST_VALUE(name) OVER (partition by ID order by salary) 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 Joe

3 Andy 300.00 Andy

3 Erick 1300.00 Erick

3 Hou 40.00 Hou

3 Kary 500.00 Kary

3 Mary 200.00 Mary

3 Secooler 800.00 Secooler

显然这不是我们想要的效果:(，这是为什么呢~~~？给您一次思考和回答的机会。

如果对UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING不熟悉，请参考Oracle官方文档“windowing_clause”。

在TEST表中添加一列，标识每一个数据分区中薪水最高的薪水值。

col highest_sal_name for 9999

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

3、与之相对应的是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