求一条SQl语句????????
A B C
a001 2006-1-1
a001 10 2006-2-1
a001 30 2006-3-1
a001 20 2006-4-1
b001 2005-1-1
b001 30 2006-2-1
b001 25.5 2006-3-1
c001 2007-3-1
c001 30 2006-5-1
c001 15 2007-2-1
c001 50 2007-1-1
---------------------------
结果：
A B C
a001 20 2006-4-1
b001 25.5 2006-3-1
c001 15 2007-2-1
取出C列中时间最大的并且B 不为空的记录(不能有A重复的记录)
[解决办法]
SQL> select * from a_max;
A B C
----- ----- ----------
a001 20060101
a001 10 20060201
a001 30 20060301
a001 20 20060401
b001 20060101
b001 30 20060201
b001 25.5 20060301
c001 20060301
c001 30 20060501
c001 15 20070201
c001 50 20070101
已选择11行。
SQL> select a.a,a.b,a.c from a_max a,
2 (select a,max(c) c_max from a_max group by a) b
3 where a.c=b.c_max and a.a=b.a
4 and a.b is not null
5 ;
A B C
----- ----- ----------
a001 20 20060401
b001 25.5 20060301
c001 15 20070201
[解决办法]
create table test (a varchar2(10),b number,c varchar2(12))
insert into test(a,c) values( 'a001 ', '2006-1-1 ');
insert into test values( 'a001 ',10, '2006-2-1 ');
insert into test values( 'a001 ',30, '2006-3-1 ');
insert into test values( 'a001 ',20, '2006-4-1 ');
insert into test(a,c) values( 'b001 ', '2005-1-1 ');
insert into test values( 'b001 ',30, '2006-2-1 ');
insert into test values( 'b001 ',25.5, '2006-3-1 ');
insert into test(a,c) values( 'c001 ', '2007-3-1 ');
insert into test values( 'c001 ',30, '2006-5-1 ');
insert into test values( 'c001 ',15, '2007-2-1 ');
insert into test values( 'c001 ',50, '2007-1-1 ');
select a.a, b,a.c from test,
(select a,max(c) c from test
where b is not null
group by a) a
where test.a = a.a and test.c = a.c
