CODE NAME DATE
0001 AAA1 201001
0001 AAA2 201007
0002 BBB1 200902
0002 BBB2 201008输入日期200907,结果如下
CODE NAME DATE
0001 AAA1 201001
0002 BBB2 201008
0001 AAA1 201001
0001 AAA2 201007
0002 BBB1 200902
0002 BBB2 201008输入日期200907,结果如下
CODE NAME DATE
0001 AAA1 201001
0002 BBB2 201008
create table t1 (CODE varchar2(20), NAME varchar2(10), t_date number(6));insert into t1 values ('0001','AAA1',201001);
insert into t1 values ('0001','AAA2',201007);
insert into t1 values ('0002','BBB1',200902);
insert into t1 values ('0002','BBB2',201008);
commit;select code,name,t_date
from
(select code,name,t_date,row_number() over(partition by code order by t_date) rn
from t1
where t_date >= 200907)
where rn=1 code name t_date
----------------------------------
1 0001 AAA1 201001
2 0002 BBB2 201008