有数据表如下:
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()的语句有点不太一样啊,他为什么会自动分组呢,!!!!!!!
有谁能帮我看看呀!!!!各位大虾们!!!