有数据表如下:
create table STUDENTS
(
ID NUMBER(15),
AREA VARCHAR2(10),
STU_TYPE VARCHAR2(2),
SCORE NUMBER(20,2)
)
数据有:
insert into students (ID, AREA, STU_TYPE, SCORE)
values (1, '111', 'g', 80.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (1, '111', 'j', 80.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (1, '222', 'g', 89.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (1, '222', 'g', 68.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (2, '111', 'g', 80.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (2, '111', 'j', 70.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (2, '222', 'g', 60.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (2, '222', 'j', 65.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (3, '111', 'g', 75.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (3, '111', 'j', 58.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (3, '222', 'g', 58.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (3, '222', 'j', 90.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (4, '111', 'g', 89.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (4, '111', 'j', 90.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (4, '222', 'g', 90.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (4, '222', 'j', 89.00); 下面是这样的:主要是对FIRST_VALUE()、LAST_VALUE()的问题
主要是Last_value()的问题!!!
select id,score,first_value(score) over(order by id) fv from students;
输出结果我可以理解:
ID SCORE FV
1 68.00 68
1 80.00 68
1 80.00 68
1 89.00 68
2 60.00 68
2 65.00 68
2 70.00 68
2 80.00 68
3 58.00 68
3 58.00 68
3 75.00 68
3 90.00 68
4 89.00 68
4 89.00 68
4 90.00 68
4 90.00 68
取的是排序的第一个值,
而在执行和他相反的语句,
select id,score,last_value(score) over(order by id) fv from students;
时的结果我有点不理解:
ID SCORE FV
1 68.00 89
1 80.00 89
1 80.00 89
1 89.00 89
2 60.00 80
2 65.00 80
2 70.00 80
2 80.00 80
3 58.00 90
3 58.00 90
3 75.00 90
3 90.00 90
4 89.00 90
4 89.00 90
4 90.00 90
4 90.00 90
为直么他取的值会是分组后每组的最后一个值呢,这和上面的First_value()的语句有点不太一样啊,他为什么会自动分组呢,!!!!!!!
有谁能帮我看看呀!!!!各位大虾们!!!
create table STUDENTS
(
ID NUMBER(15),
AREA VARCHAR2(10),
STU_TYPE VARCHAR2(2),
SCORE NUMBER(20,2)
)
数据有:
insert into students (ID, AREA, STU_TYPE, SCORE)
values (1, '111', 'g', 80.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (1, '111', 'j', 80.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (1, '222', 'g', 89.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (1, '222', 'g', 68.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (2, '111', 'g', 80.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (2, '111', 'j', 70.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (2, '222', 'g', 60.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (2, '222', 'j', 65.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (3, '111', 'g', 75.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (3, '111', 'j', 58.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (3, '222', 'g', 58.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (3, '222', 'j', 90.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (4, '111', 'g', 89.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (4, '111', 'j', 90.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (4, '222', 'g', 90.00); insert into students (ID, AREA, STU_TYPE, SCORE)
values (4, '222', 'j', 89.00); 下面是这样的:主要是对FIRST_VALUE()、LAST_VALUE()的问题
主要是Last_value()的问题!!!
select id,score,first_value(score) over(order by id) fv from students;
输出结果我可以理解:
ID SCORE FV
1 68.00 68
1 80.00 68
1 80.00 68
1 89.00 68
2 60.00 68
2 65.00 68
2 70.00 68
2 80.00 68
3 58.00 68
3 58.00 68
3 75.00 68
3 90.00 68
4 89.00 68
4 89.00 68
4 90.00 68
4 90.00 68
取的是排序的第一个值,
而在执行和他相反的语句,
select id,score,last_value(score) over(order by id) fv from students;
时的结果我有点不理解:
ID SCORE FV
1 68.00 89
1 80.00 89
1 80.00 89
1 89.00 89
2 60.00 80
2 65.00 80
2 70.00 80
2 80.00 80
3 58.00 90
3 58.00 90
3 75.00 90
3 90.00 90
4 89.00 90
4 89.00 90
4 90.00 90
4 90.00 90
为直么他取的值会是分组后每组的最后一个值呢,这和上面的First_value()的语句有点不太一样啊,他为什么会自动分组呢,!!!!!!!
有谁能帮我看看呀!!!!各位大虾们!!!
解决方案 »
- DB2中怎样把同一张表中相同字段的内容合并为一条记录
- 三张数据量不算大的表关联效率很低的原因
- 求PLSQL Developer7.1.5.1399版本的产品编号,序列号,口令
- SQL语句报表
- oracle developer2000是什么
- weblogic8与oracle10配的连接池取不到连接
- 升四星了,多谢兄弟们的鼓励与支持!
- 关于的utl_file.fopen错误!
- 给50分:很简单的问题确认!!
- 字段汇总语句的一个小问题,即sum的使用
- 问一个查询的问题,请求达人帮忙!
- 日期运算begin_Time:=end_Time-interval '1' year中如何使得时间间隔数和时间单位参数化
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
加上红色看看
同意,range between unbounded preceding and current row是默认的窗口范围,如果你没有指定就是使用默认窗口范围,所以first_value和last_value的结果和你想像的差异就出来了。
想问一下,我按ID排序,然后有四个记录为ID为1的,如果他上面没有记录,这四个记录的last_value就取就后一个的值是吗?