高手帮忙看看SQL怎么写

Question

有如下表：
select * from t1 --项目收款表
mon_id  pro_id  rev_am
200803 a 1000
200805 a 2000
200803 b 100
200805 b 200select * from t2 --项目付款表
mon_id  pro_id  paid_am
200802 a 3000
200806 a 4000
200802 c 300
200806 c 400select * from pro_info --项目基本信息表
pro_id  con_begin_time  con_end_time
a 20080101 20080601
b 20080201 20080801
c 20080101 20081001select * from time_table --时间表
mon_id
200801
200802
200803
200804
200805
200806
200807
200808
200809
200810现在要求得到各个项目起止时间范围内的项目收款付款累计情况。即希望写几条SQL语句，能得到如下结果：
mon_id  pro_id  rev_am  paid_am
200801  a       
200802  a               3000
200803  a       1000
200804  a       1000    3000          --因为3月已经收到了钱，2月已经付了钱，而4月又没收钱又没付钱，所以4月的应该是以前的累积。
200805  a       3000    3000          --因为4月又收了2000，所以到4月的累计值应该是3000。
200806  a       3000    7000          --因为项目a的起止时间是200801——200806，所以项目a到此结束。
200802  b                             --项目b同项目a一样的算法。
...希望大家看过之后会明白。这个SQL偶研究了好久，没有研究出来。希望高手能帮忙看看。谢谢！

Answer 1

--建立测试环境
 
create table t1(mon_id varchar(20),pro_id varchar(20),rev_am int);
insert into t1 select '200803','a','1000' from dual;
insert into t1 select '200805','a','2000'from dual;
insert into t1 select '200803','b','100'from dual;
insert into t1 select '200805','b','200'from dual;
 
create table t2(mon_id varchar(20),pro_id varchar(20),paid_am int);
insert into t2 select '200802','a','3000'from dual;
insert into t2 select '200806','a','4000'from dual;
insert into t2 select '200802','c','300'from dual;
insert into t2 select '200806','c','400'from dual;create table pro_info(pro_id varchar(20),con_begin_time varchar(20),con_end_time varchar(20));
insert into pro_info select 'a','20080101','20080601'from dual;
insert into pro_info select 'b','20080201','20080801'from dual;
insert into pro_info select 'c','20080101','20081001'from dual;create table time_table(mon_id varchar(20));
insert into time_table select '200801'from dual;
insert into time_table select '200802'from dual;
insert into time_table select '200803'from dual;
insert into time_table select '200804'from dual;
insert into time_table select '200805'from dual;
insert into time_table select '200806'from dual;
insert into time_table select '200807'from dual;
insert into time_table select '200808'from dual;
insert into time_table select '200809'from dual;
insert into time_table select '200810'from dual;
--测试语句select t.*,
(select sum(rev_am) from t1 where mon_id<=t.mon_id and pro_id=t.Pro_id) as rev_am,
(select sum(paid_am) from t2 where mon_id<=t.mon_id and pro_id=t.Pro_id) as paid_am
 from(select a.mon_id,b.pro_id
 from time_table a , pro_info b where a.mon_id between b.con_begin_time and b.con_end_time)t
 order by t.pro_id, t.mon_id
--删除测试环境
--drop table t1;
--drop table t2;
--drop table pro_info;
--drop table time_table;/*
MON_ID PRO_ID REV_AM PAID_AM
200802 a 3000
200803 a 1000 3000
200804 a 1000 3000
200805 a 3000 3000
200806 a 3000 7000
200803 b 100
200804 b 100
200805 b 300
200806 b 300
200807 b 300
200808 b 300
200802 c 300
200803 c 300
200804 c 300
200805 c 300
200806 c 700
200807 c 700
200808 c 700
200809 c 700
200810 c 700
%/

Answer 2

给你搞出来了SQL> select mon_id,
  2         pro_id,
  3         sum(rev_am) over(partition by pro_id order by mon_id asc) rev_am,
  4         sum(paid_am) over(partition by pro_id order by mon_id asc) paid_am
  5    from (select c.mon_id, c.pro_id, t1.rev_am, t2.paid_am
  6            from (select mon_id, b.pro_id
  7                    from time_table a, pro_info b
  8                   where a.mon_id between substr(con_begin_time, 1, 6) and
  9                         substr(con_end_time, 1, 6)
 10                   order by 2, 1) c,
 11                 t1,
 12                 t2
 13           where c.mon_id = t1.mon_id(+)
 14             and c.pro_id = t1.pro_id(+)
 15             and c.mon_id = t2.mon_id(+)
 16             and c.pro_id = t2.pro_id(+)
 17           order by 2, 1);
 
MON_ID               PRO_ID                   REV_AM    PAID_AM
-------------------- -------------------- ---------- ----------
200801               a                               
200802               a                                     3000
200803               a                          1000       3000
200804               a                          1000       3000
200805               a                          3000       3000
200806               a                          3000       7000
200802               b                               
200803               b                           100 
200804               b                           100 
200805               b                           300 
200806               b                           300 
200807               b                           300 
200808               b                           300 
200801               c                               
200802               c                                      300
200803               c                                      300
200804               c                                      300
200805               c                                      300
200806               c                                      700
200807               c                                      700
200808               c                                      700
200809               c                                      700
200810               c                                      700
 
23 rows selected