求一条sql语句

[骑驴快跑]

表1、表2用进行关联，如果表2的日期如果出现在表1cq、tq这个范围内，xs累加重新计算，后面没有则给0，如果没在就一直累加
表1

id		gh		      cq		                  tq     		xs
1		101		2016/10/21		2016/10/22		4
2		101		2016/11/1		2016/11/2		3
3		102		2016/5/9		2016/5/10		2
4		103		2016/8/10		2016/8/11		6
5		104		2016/9/10		2016/9/10		7
6		102		2016/6/9		2016/6/10		4
7		103		2016/9/1		2016/9/2		5
8		104		2016/10/10		2016/10/13		6
9		105		2017/2/2		2017/2/4		9
10		107		2016/10/2		2016/10/3		11
11		102		2016/10/10		2016/10/10		23
12		103		2016/12/11		2016/12/12		6
13		105		2017/2/5		2017/2/5		7
14		108		2017/2/3		2017/2/4		12
15		106		2016/4/9		2016/4/10		6
16		108		2017/3/3		2017/3/4		4
17		104		2016/11/18		2016/11/18		8
18		102		2016/11/9		2016/11/10		6
19		103		2017/2/10		2017/2/11		3
20		105		2017/3/20		2017/3/20		6

表2

id	gh	            rq
1	101	      2016/11/2
2	107	      2016/10/3
3	104	      2016/10/13

得到结果

id	gh	     xs
1	101	      0
2	102	      35
3	103	      20
4	104		8
5	105		22
6	106		6
7	107	    	0
8	108		16

[tiegenZ]

表1和表2 是通过哪个字段关联的？？

[AHUA1001]

select rownum,t.* from (select t1.gh,sum(t1.tq) from t1 join t2 on t2.rq between t1.cq开始时间 and t1.cq结束时间 order by t1.gh) t

[Iris945]

with t as
(
select t2.gh,sum(case when t2.rq between t1.cq and t1.tq then xs else 0 end)sum_xs 
from t2 
left join t1 on t2.gh=t1.gh 
group by t2.gh
)
select rownum id,gh,sum_xs xs from t
;

[imcmuc]

表1、表2用进行关联，如果表2的日期如果出现在表1cq、tq这个范围内，xs累加重新计算，后面没有则给0，如果没在就一直累加 这点对我来说用一条sql真的很难，就写了一个存储过程，最后输出的就是结果。

CREATE OR REPLACE PROCEDURE CUL AS
  NUMB  NUMBER := 0;
  GHID  VARCHAR2(20);
  XSNUM NUMBER;
BEGIN
  DBMS_OUTPUT.put_line('ID GH XS');
  FOR ROWZS IN (SELECT DISTINCT GH FROM TABLE1 ORDER BY GH) -- 取GH
   LOOP
    GHID  := ROWZS.GH;
    XSNUM := 0;
    NUMB  := NUMB + 1;
    FOR ZSROW IN (SELECT * FROM TABLE1 TT WHERE TT.GH = GHID ORDER BY ID) LOOP --遍历相同GH的数据
      XSNUM := XSNUM + ZSROW.XS;
      FOR JSZROW IN (SELECT * FROM TABLE2 T WHERE T.GH = GHID) --取GH在TABLE2中的数据
       LOOP
        IF JSZROW.RQ BETWEEN ZSROW.CQ AND ZSROW.TQ THEN
          XSNUM := 0;
        END IF;
      END LOOP;
    END LOOP;
    DBMS_OUTPUT.put_line(NUMB || ' ' || GHID || ' ' || XSNUM);
  END LOOP;
END;