求一条SQl语句????????

[yumei20001]

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重复的记录)

[letqqqgo]

这样：
select
last_value(a) over(partition by a order by c) as a,
last_value(b) over(partition by a order by c) as b,
last_value(c) over(partition by a order by c) as c
from ±í
where b is not null

[chd2001]

select * from tab t where not exists(
select 1 from tab whee t.a=a and t.b is not null and c>t.c)

[yumei20001]

刚才运行了一下,都不对啊?

[chd2001]

select * from tab t where not exists(
select 1 from tab whee t.a=a and c>t.c) and t.b is not null

[jdsnhan]

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

[Eric_1999]

select
last_value(a) over(partition by a order by c) as a,
last_value(b) over(partition by a order by c) as b,
last_value(c) over(partition by a order by c) as c
from (select * from tab where b is not null)

[anne]

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

[anne]

不知道会不会因为C列数据类型不同而有不足之处,请楼主试试.