create table test1(id varchar2(10),name varchar2(10),age varchar2(10));
insert into test1(id,name,age)values(1,'jime','10');
insert into test1(id,name,age)values(2,'marry','20');
insert into test1(id,name,age)values(2,'tom','9');
insert into test1(id,name,age)values(3,'kate','7');
insert into test1(id,name,age)values(1,'broone','5');目前我用如下语句得到了部分结果,
select id,min(age) age from test1
group by id
id age
1 10
2 20
3 7
现在需要,
如下结果,其oracle sql语句该如何写?
id age name
1 10 jime
2 20 marry
3 7 kate
insert into test1(id,name,age)values(1,'jime','10');
insert into test1(id,name,age)values(2,'marry','20');
insert into test1(id,name,age)values(2,'tom','9');
insert into test1(id,name,age)values(3,'kate','7');
insert into test1(id,name,age)values(1,'broone','5');目前我用如下语句得到了部分结果,
select id,min(age) age from test1
group by id
id age
1 10
2 20
3 7
现在需要,
如下结果,其oracle sql语句该如何写?
id age name
1 10 jime
2 20 marry
3 7 kate
from test1 a,
(
select id,min(age) age from test1
group by id
) b
where a.id=b.id and a.age=b.age
select id,name,age
from
(
select a.*,row_number() over(partition by id order by age) rn from test1 a
) a
where rn=1
顶hebo2005
另第一种方法改成max就哦了.
from
(
select a.*,row_number() over(partition by id order by age) rn from test1 a
) a
where rn=1
select a.*
from test1 a,
(
select id,MAX(age) age from test1
group by id
) b
where a.id=b.id and a.age=b.age[code=SQL]
2 from test1 t
3 where (t.id,t.age) in
4 (select id,min(age) age from test1
5 group by id);ID AGE NAME
---------- ---------- ----------
1 10 jime
2 20 marry
3 7 kateSQL>