select * from
(select a.jh as jh,sum(nvl(dba02.rpzsl,0)) as rpzsl,sum(nvl(dba02.rzsl,0)) as rzsl from dba02, (select dm, jh from daa01 where jh like'双泌3%') a where rq between to_date('2003/08/01','yyyy-mm-dd') and to_date('2003/08/02','yyyy-mm-dd') and dba02.jh = a.jh group by a.jh)
结果：jh rpzsl rzsl
31 90 41
32 155 110
33 12 32
34 32 321
35 47 92
38 322 786
将上述sql语句的日期变为：从2005/08/01到2005/08/03后，执行结果为：
jh rpzsl rzsl
31 90 41
33 12 32
34 32 321
35 88 98
36 23 47
38 322 786
现在我想实现上述两种结果的比较，sql语句如下：
select *
from
(select a.jh as jh,sum(nvl(dba02.rpzsl,0)) as rpzsl,sum(nvl(dba02.rzsl,0)) as rzsl from dba02, (select dm, jh from daa01 where jh like'双泌3%') a where rq between to_date('2003/08/01','yyyy-mm-dd') and to_date('2003/08/02','yyyy-mm-dd') and dba02.jh = a.jh group by a.jh)b
full outer join
(select a.jh as jh,sum(nvl(dba02.rpzsl,0)) as rpzsl,sum(nvl(dba02.rzsl,0)) as rzsl from dba02,(select dm,jh from daa01 where jh like'双泌3%')a where rq between to_date ('2005/08/01','yyyy-mm-dd')and to_date('2005/08/03','yyyy-mm-dd') and dba02.jh = a.jh group by a.jh) c
on b.jh=c.jh
可是出来的结果却是：
jh rpzsl rzsl jh_1 rpzsl_1 rzsl_1
31 90 41 31 90 41
33 12 32 33 12 32
34 32 321 34 32 321
35 47 92 35 88 98
38 322 786 38 322 786
32 155 110 null null null
null null null 36 null null
null null null 36 null null
null null null 36 null null
我期望的结果是
jh rpzsl rzsl jh_1 rpzsl_1 rzsl_1
31 90 41 31 90 41
33 12 32 33 12 32
34 32 321 34 32 321
35 47 92 35 88 98
38 322 786 38 322 786
32 155 110 null null null
null null null 36 23 47
请问大家，怎样实现呢？
现在我分析好像是Full Outer Join 和Sum函数的联合使用的问题。
---------------------------------------------------------------
不知道这个行不行
select jh ,
sum(decode(to_char(rq,'yyyy'),2003,nvl(dba02.rpzsl,0),0)) as rpzsl,
sum(decode(to_char(rq,'yyyy'),2003,nvl(dba02.rzsl,0),0)) as rzsl ,
jh ,
sum(decode(to_char(rq,'yyyy'),2005,nvl(dba02.rpzsl,0),0)) as rpzsl,
sum(decode(to_char(rq,'yyyy'),2005,nvl(dba02.rzsl,0),0)) as rzsl
from dba02
where
jh in (select jh from daa01) and jh like'双泌3%'
and
( rq between to_date('2003/08/01','yyyy-mm-dd') and to_date('2003/08/02','yyyy-mm-dd') or
rq between to_date('2005/08/01','yyyy-mm-dd') and to_date('2005/08/02','yyyy-mm-dd') )
group by jh