ORACEL
的分析函数:可以取出分组中第一个(最后一个)的数据
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
create
table ATEM
(
CO1 VARCHAR2( 10 ),
CO2 INTEGER
)
(
CO1 VARCHAR2( 10 ),
CO2 INTEGER
)
insert
into atem(co1,co2) values(
'a'
,
1
);
insert into atem(co1,co2) values( 'a' , 2 );
insert into atem(co1,co2) values( 'a' , 3 );
insert into atem(co1,co2) values( 'a' , 4 );
insert into atem(co1,co2) values( 'a' , 1 );
insert into atem(co1,co2) values( 'b' , 1 );
insert into atem(co1,co2) values( 'b' , 2 );
insert into atem(co1,co2) values( 'c' , 1 );
insert into atem(co1,co2) values( 'd' , 1 );
insert into atem(co1,co2) values( 'd' , 2 );
insert into atem(co1,co2) values( 'a' , 2 );
insert into atem(co1,co2) values( 'a' , 3 );
insert into atem(co1,co2) values( 'a' , 4 );
insert into atem(co1,co2) values( 'a' , 1 );
insert into atem(co1,co2) values( 'b' , 1 );
insert into atem(co1,co2) values( 'b' , 2 );
insert into atem(co1,co2) values( 'c' , 1 );
insert into atem(co1,co2) values( 'd' , 1 );
insert into atem(co1,co2) values( 'd' , 2 );
![](https://p-blog.csdn.net/images/p_blog_csdn_net/chenjing0718/98d0f4a09be740a0a41abd91d818268f.png)
select
distinct(FIRST_VALUE(a.co2)OVER(PARTITION BY a.co1 ORDER BY a.co2)) as co2,a.co1
from atem a
from atem a
// FIRST_VALUE(a.co2)OVER(PARTITION BY a.co1 ORDER BY a.co2)
//
在表中以
a.co1
分组,并在分组中以
a.co2
排序,最后要得到分组中
a.co2
的数据