表 A CIFNO,OPENDATE
1 2012-2-16
1 2012-4-16
2 2012-5-16
2 2012-6-16
3 2012-9-16
3 2012-4-16
3 2012-3-16
怎么写取出结果为
表 A CIFNO,OPENDATE
1 2012-2-16
2 2012-5-16
3 2012-3-16
1 2012-2-16
1 2012-4-16
2 2012-5-16
2 2012-6-16
3 2012-9-16
3 2012-4-16
3 2012-3-16
怎么写取出结果为
表 A CIFNO,OPENDATE
1 2012-2-16
2 2012-5-16
3 2012-3-16
create table A (CIFNO number(10),OPENDATE date);insert into A values (1,date'2012-02-16');
insert into A values (1,date'2012-04-16');
insert into A values (2,date'2012-05-16');
insert into A values (2,date'2012-06-16');
insert into A values (3,date'2012-09-16');
insert into A values (3,date'2012-04-16');
insert into A values (3,date'2012-03-16');select CIFNO,OPENDATE from
(select CIFNO,OPENDATE,row_number() over(partition by CIFNO order by OPENDATE) rn
from A)
where rn=1 CIFNO OPENDATE
-------------------------------------
1 1 2012/2/16
2 2 2012/5/16
3 3 2012/3/16
试试
from 表A
where rn = 1
with a as(
select 1 cifno,to_date('2012-2-16','yyyy-mm-dd') opendate from dual
union all
select 1,to_date('2012-4-16','yyyy-mm-dd') from dual
union all
select 2,to_date('2012-5-16','yyyy-mm-dd') from dual
union all
select 2,to_date('2012-6-16','yyyy-mm-dd') from dual
union all
select 3,to_date('2012-9-16','yyyy-mm-dd') from dual
union all
select 3,to_date('2012-4-16','yyyy-mm-dd') from dual
union all
select 3,to_date('2012-3-16','yyyy-mm-dd') from dual
)
select cifno,min(opendate) from a group by cifno;
CIFNO MIN(OPENDATE)
---------- -------------
1 2012-2-16
2 2012-5-16
3 2012-3-16
from A
group by cifno慢慢的练习用用分析函数编程, 虽然上面的简单明了,学习一下:with a as(
select 1 cifno,to_date('2012-2-16','yyyy-mm-dd') opendate from dual
union all
select 1,to_date('2012-4-16','yyyy-mm-dd') from dual
union all
select 2,to_date('2012-5-16','yyyy-mm-dd') from dual
union all
select 2,to_date('2012-6-16','yyyy-mm-dd') from dual
union all
select 3,to_date('2012-9-16','yyyy-mm-dd') from dual
union all
select 3,to_date('2012-4-16','yyyy-mm-dd') from dual
union all
select 3,to_date('2012-3-16','yyyy-mm-dd') from dual
)select cifno, opendate
from(
select cifno, opendate, rank() over(partition by cifno order by opendate) rn_ from a
) ta
where ta.rn_ = 11 16-FEB-12
2 16-MAY-12
3 16-MAR-12