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
8 个解决方案
#1
oracle 没有 top关键字 你可以用rownum=1
#2
select * from (select c.cifno,c.opendate from A c group by c.cifno,c.opendate order by c.opendate ) x
where rownum=1;
我写了。。。不对。。所以来求教
#3
select
CIFNO,
OPENDATE,
dense_rank() over(partition by CIFNO order by OPENDATE) rank
from (select c.cifno,c.opendate from A c group by c.cifno,c.opendate order by c.opendate ) x
where rank=1;
CIFNO,
OPENDATE,
dense_rank() over(partition by CIFNO order by OPENDATE) rank
from (select c.cifno,c.opendate from A c group by c.cifno,c.opendate order by c.opendate ) x
where rank=1;
#4
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
#5
select distinct CIFNO,OPENDATE
from A t1
where OPENDATE=(select min(OPENDATE) from A where a.cifno=t1.cifno)
order by CIFNO
CIFNO OPENDATE
-------------------------------------
1 1 2012/2/16
2 2 2012/5/16
3 3 2012/3/16
#6
SELECT CIFNO,Min(OPENDATE) FROM A GROUP BY CIFNO
CIFNO MIN(OPENDATE)
1 16.02.2012 00:00:00
2 16.05.2012 00:00:00
3 16.03.2012 00:00:00
#7
select * from A a1 where not exists (select 1 from A a2 where a1.CIFNO=a2.CIFNO and a2.OPENDATE > a1.OPENDATE);
#8
学习了
#1
oracle 没有 top关键字 你可以用rownum=1
#2
select * from (select c.cifno,c.opendate from A c group by c.cifno,c.opendate order by c.opendate ) x
where rownum=1;
我写了。。。不对。。所以来求教
#3
select
CIFNO,
OPENDATE,
dense_rank() over(partition by CIFNO order by OPENDATE) rank
from (select c.cifno,c.opendate from A c group by c.cifno,c.opendate order by c.opendate ) x
where rank=1;
CIFNO,
OPENDATE,
dense_rank() over(partition by CIFNO order by OPENDATE) rank
from (select c.cifno,c.opendate from A c group by c.cifno,c.opendate order by c.opendate ) x
where rank=1;
#4
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
#5
select distinct CIFNO,OPENDATE
from A t1
where OPENDATE=(select min(OPENDATE) from A where a.cifno=t1.cifno)
order by CIFNO
CIFNO OPENDATE
-------------------------------------
1 1 2012/2/16
2 2 2012/5/16
3 3 2012/3/16
#6
SELECT CIFNO,Min(OPENDATE) FROM A GROUP BY CIFNO
CIFNO MIN(OPENDATE)
1 16.02.2012 00:00:00
2 16.05.2012 00:00:00
3 16.03.2012 00:00:00
#7
select * from A a1 where not exists (select 1 from A a2 where a1.CIFNO=a2.CIFNO and a2.OPENDATE > a1.OPENDATE);
#8
学习了