17,086
社区成员
发帖
与我相关
我的任务
分享
create table test4
(well_id varchar2(10),
prod_date date,
val number);
insert into test4 values('A',to_date('2010-6-1','yyyy-mm-dd'),1);
insert into test4 values('A',to_date('2010-6-1','yyyy-mm-dd'),2);
insert into test4 values('A',to_date('2010-6-1','yyyy-mm-dd'),3);
insert into test4 values('A',to_date('2010-6-1','yyyy-mm-dd'),4);
insert into test4 values('A',to_date('2010-6-3','yyyy-mm-dd'),5);
insert into test4 values('A',to_date('2010-6-3','yyyy-mm-dd'),6);
insert into test4 values('A',to_date('2010-6-3','yyyy-mm-dd'),7);
insert into test4 values('A',to_date('2010-6-3','yyyy-mm-dd'),8);
insert into test4 values('A',to_date('2010-6-5','yyyy-mm-dd'),9);
insert into test4 values('A',to_date('2010-6-5','yyyy-mm-dd'),10);
insert into test4 values('A',to_date('2010-6-5','yyyy-mm-dd'),11);
insert into test4 values('A',to_date('2010-7-1','yyyy-mm-dd'),12);
insert into test4 values('A',to_date('2010-7-1','yyyy-mm-dd'),13);
insert into test4 values('A',to_date('2010-7-1','yyyy-mm-dd'),14);
commit;
select b.* from test4 b
where exists
(select 1 from
(select well_id,prod_date from
(select distinct well_id,prod_date from test4
where prod_date <= to_date('2010-7-3','yyyy-mm-dd')
order by prod_date desc) where rownum<=3) a
where a.well_id = b.well_id and a.prod_date = b.prod_date);
select well_id,prod_date,val from(
select well_id,prod_date,val,DENSE_RANK() over(partition by well_id order by prod_date desc) rn from test4
where prod_date <= to_date('2010-7-3','yyyy-mm-dd')
) where rn <= 3