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 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函数的联合使用的问题。
from b,c
where b.jh=c.jh(+)
union
select *
from b,c
where b.jh(+)=c.jh
b的条件是从2005/08/01到2005/08/02后,结果应该在c从2005/08/01到2005/08/03之中。
b:2003年 c:2005年 :)还有,我把sum移到顶层select中求和,结果出来后是正确的,可是这样的执行效率非常慢。
我想可以把你的b,c并在一块,这样可能少层嵌套,你把dba02和daa01的基本结构给一下吧
JH VARCHAR2(16) NOT NULL,
YT VARCHAR2(10) NULL,
DM VARCHAR2(10) NULL,
QK VARCHAR2(10) NULL,
DY VARCHAR2(10) NULL,
CW VARCHAR2(50) NULL,
SKJDDS1 NUMBER(7,2) NULL,
CYRQ DATE NULL,
SKJDDS2 NUMBER(7,2) NULL,
CQRQ DATE NULL,
ZQRQ DATE NULL,
QQRQ DATE NULL,
HSRQ DATE NULL,
SKYCDS1 NUMBER(7,2) NULL,
SKYCDS2 NUMBER(7,2) NULL,
SCBZ CHAR(1) NULL,
JSHD NUMBER(5,1) NULL,
YXHD NUMBER(5,1) NULL,
CS NUMBER(2,0) NULL,
MQJB CHAR(2) NULL,
SJJB CHAR(2) NULL,
TCJB CHAR(2) NULL,
TCRQ DATE NULL,
ZZRQ DATE NULL,
CM VARCHAR2(10) NULL,
KM VARCHAR2(10) NULL,
YPFL CHAR(1) NULL,
ZSRQ DATE NULL,
JSRQ1 DATE NULL,
YSDCYL NUMBER(5,2) NULL,
YSDCWD NUMBER(5,1) NULL,
BGRQ DATE NULL,
SYQK VARCHAR2(10) NULL,
LHZH VARCHAR2(16) NULL,
JRJLZBZ CHAR(1) NULL,
YLYXHD NUMBER(5,1) NULL,
ZSZ VARCHAR2(10) NULL,
RLYXHD NUMBER(5,1) NULL,
BHYL NUMBER(5,2) NULL,
DYXH NUMBER(3,0) NULL,
BZ VARCHAR2(30) NULL,
JHDM VARCHAR2(16) NULL,
DZQK VARCHAR2(20) NULL,
QK_JLZH VARCHAR2(20) NULL,
JLZXH VARCHAR2(3) NULL,
DMXH VARCHAR2(1) NULL,
QKXH VARCHAR2(1) NULL,
JHXH VARCHAR2(10) NULL,
QKHZ VARCHAR2(16) NULL,
JSRQ DATE NULL,
CYJH VARCHAR2(16) NULL,
JHBM VARCHAR2(16) NULL,
YQTDM VARCHAR2(5) NULL,
DWMC VARCHAR2(40) NULL,
ZQRQ1 DATE NULL,
ZJRQ DATE NULL,
BFRQ DATE NULL,
BFYY VARCHAR2(20) NULL,
ZSZBSD NUMBER(7,2) NULL,
DZCL NUMBER(8,2) NULL,
SCQSRQ DATE NULL,
SCJXRQ DATE NULL
,PRIMARY KEY(JH)
)Create Table DBA02(
JH VARCHAR2(16) NOT NULL,
RQ DATE NOT NULL,
DYDM CHAR(15) NULL,
FZYL NUMBER(5,2) NULL,
HGYL NUMBER(5,2) NULL,
ZSFS CHAR(3) NULL,
SCSJ NUMBER(5,2) NULL,
ZRYL NUMBER(4,0) NULL,
ZJHWFZL NUMBER(5,2) NULL,
RZGFL NUMBER(5,2) NULL,
ZRYND NUMBER(5,2) NULL,
TY NUMBER(5,2) NULL,
ZRYND1 NUMBER(7,1) NULL,
YY NUMBER(5,2) NULL,
GXYL NUMBER(5,2) NULL,
ZRSWD NUMBER(4,0) NULL,
PZCDS NUMBER(1,0) NULL,
YLL NUMBER(3,0) NULL,
RPZSL NUMBER(5,1) NULL,
RZSL NUMBER(5,1) NULL,
JKHT NUMBER(4,2) NULL,
JKZZ NUMBER(5,2) NULL,
CD1RZ NUMBER(5,1) NULL,
CD2RZ NUMBER(5,1) NULL,
CD3RZ NUMBER(5,1) NULL,
XJFS CHAR(1) NULL,
JKSL NUMBER(4,0) NULL,
CKSL NUMBER(4,0) NULL,
PLL NUMBER(4,0) NULL,
BZDM CHAR(4) NULL,
CD4RZ NUMBER(4,0) NULL,
CD5RZ NUMBER(4,0) NULL,
CD6RZ NUMBER(4,0) NULL,
CD7RZ NUMBER(4,0) NULL,
BY1 NUMBER(5,2) NULL,
ZRYJKND NUMBER(5,2) NULL,
ZRYJKND1 NUMBER(7,1) NULL,
ZJSJ NUMBER(4,1) NULL,
CW VARCHAR2(15) NULL,
DWDM CHAR(11) NULL,
YLJZSL NUMBER(6,0) NULL,
YLJZSSJ NUMBER(7,4) NULL,
ZSXZ CHAR(1) NULL,
BZ VARCHAR2(60) NULL,
JHDM CHAR(16) NULL,
XJJKSL NUMBER(4,0) NULL,
XJCKSL NUMBER(4,0) NULL,
DWMC VARCHAR2(20) NULL,
ZSCW VARCHAR2(30) NULL,
JKWD NUMBER(5,1) NULL,
CDXZ CHAR(1) NULL,
SCLX CHAR(1) NULL,
RCBZ VARCHAR2(2) NULL,
BZDM1 VARCHAR2(4) NULL
,PRIMARY KEY(JH,RQ)
)
select *
from b,c
where b.jh=c.jh(+)
union
select *
from b,c
where b.jh(+)=c.jh可否优化为b,c子表只执行查询一次
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
这样是可以的,可是我感觉效率还是不高:|
我想把您的第一个sql语句优化为b,c子表只执行查询一次的效率会很高,而且会很灵活
let me try
第二次查询b,c时,速度很快
经过考虑,我还是选择了您的第一个方案根据资料,ora9i已经对full outer join支持了,经过这个问题,看来支持的并不完善。 :)