3,490
社区成员
发帖
与我相关
我的任务
分享
用select ... from t left join pro_info a on a.pro_id=t.pro_id
create table pro_info (pro_id varchar(20),pro_begin_time varchar(20),
pro_end_time varchar(20),next_pay_day varchar(20),next_pay_money int);
insert into pro_info select 'A','20080101','20080601','20080207','100' from dual;
insert into pro_info select 'A','20080101','20080601','20080307','200' from dual;
insert into pro_info select 'A','20080101','20080601','20080607','300' from dual;
--建立测试环境
create table pro_info (pro_id varchar(20),pro_begin_time varchar(20),
pro_end_time varchar(20),next_pay_day varchar(20),next_pay_money int);
insert into pro_info select 'A','20080101','20080601','20080207','100' from dual;
insert into pro_info select 'A','20080101','20080601','20080307','200' from dual;
insert into pro_info select 'A','20080101','20080601','20080607','300' from dual;
create table t(月份 varchar(20),项目ID varchar(20),已收金额 int);
insert into t select '200801','A',null from dual;
insert into t select '200802','A','100' from dual;
insert into t select '200803','A','300' from dual;
insert into t select '200804','A','300' from dual;
insert into t select '200805','A','300' from dual;
insert into t select '200806','A','600' from dual;
--测试语句
select 月份,项目ID,已收金额,next_pay_day,next_pay_money from(
select t.*,a.* ,row_number() over(partition by 月份,项目ID order by next_pay_day) as rn
from t left join pro_info a on a.next_pay_day>t.月份 and a.next_pay_day not like t.月份||'%'
order by t.月份)b
where b.rn=1;
--删除测试环境
--drop table pro_info;
--drop table t;
/*--测试结果
月份 项目ID 已收金额 NEXT_PAY_DAY NEXT_PAY_MONEY
200801 A 20080207 100
200802 A 100 20080307 200
200803 A 300 20080607 300
200804 A 300 20080607 300
200805 A 300 20080607 300
200806 A 600
*/