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重复的记录)
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重复的记录)
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
select 1 from tab whee t.a=a and t.b is not null and c>t.c)
select 1 from tab whee t.a=a and c>t.c) and t.b is not null
----- ----- ----------
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
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)
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