select a.huoh,round(sum(nvl(a.shul,0)-nvl(b.shul,0)),2) shul, round(sum(nvl(a.jine,0)-nvl(b.jine,0)),2) jine, decode(sum(nvl(a.shul,0)-nvl(b.shul,0)),0,0,round(sum(nvl(a.jine,0)-nvl(b.jine,0))/sum(nvl(a.shul,0)-nvl(b.shul,0)),2)) danj from (select huoh ,nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine from xs.cangkmxlsz where zhudcwh like '00%' and (beidcwh between '010001' and '010020' or beidcwh > '010030') and denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and caozlx = '01' and huoh like '00%' group by huoh) a, ( select huoh, nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine from xs.cangkmxlsz where beidcwh like '00%' and (zhudcwh between '010001' and '010020' or zhudcwh > '010030') and denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and caozlx = '01' and huoh like '00%' group by huoh) b where a.huoh=b.huoh(+) group by a.huoh union all select b.huoh,round(sum(nvl(a.shul,0)-nvl(b.shul,0)),2) shul, round(sum(nvl(a.jine,0)-nvl(b.jine,0)),2) jine, decode(sum(nvl(a.shul,0)-nvl(b.shul,0)),0,0,round(sum(nvl(a.jine,0)-nvl(b.jine,0))/sum(nvl(a.shul,0)-nvl(b.shul,0)),2)) danj from (select huoh ,nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine from xs.cangkmxlsz where zhudcwh like '00%' and (beidcwh between '010001' and '010020' or beidcwh > '010030') and denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and caozlx = '01' and huoh like '00%' group by huoh) a, ( select huoh, nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine from xs.cangkmxlsz where beidcwh like '00%' and (zhudcwh between '010001' and '010020' or zhudcwh > '010030') and denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and caozlx = '01' and huoh like '00%' group by huoh) b where a.huoh(+)=b.huoh group by b.huoh 其中一条语句大概有两千多行,一条有五十左右
回避一下union ,试下面的结果:select huoh,round(sum(nvl(shul_a,0)-nvl(shul_b,0)),2) shul, round(sum(nvl(jine_a,0)-nvl(jine_b,0)),2) jine, decode(sum(nvl(shul_a,0)-nvl(shul_b,0)),0,0, round(sum(nvl(jine_a,0)-nvl(jine_b,0)) /sum(nvl(shul_a,0)-nvl(shul_b,0)),2)) danj (select huoh,decode(substr(zhudcwh,1,2),'00',shul,0) shul_a, decode(substr(zhudcwh,1,2),'00',zhixj * shul,0) jine_a, decode(substr(beidcwh,1,2),'00',shul,0) shul_b, decode(substr(beidcwh,1,2),'00',zhixj * shul,0) jine_b from xs.cangkmxlsz where (denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and caozlx = '01' and huoh like '00%') and ((zhudcwh like '00%' and (beidcwh between '010001' and '010020' or beidcwh > '010030')) or (beidcwh like '00%' and (zhudcwh between '010001' and '010020' or zhudcwh > '010030'))) ) group by huoh
select huoh,round(sum(nvl(shul_a,0)-nvl(shul_b,0)),2) shul, round(sum(nvl(jine_a,0)-nvl(jine_b,0)),2) jine, decode(sum(nvl(shul_a,0)-nvl(shul_b,0)),0,0, round(sum(nvl(jine_a,0)-nvl(jine_b,0)) /sum(nvl(shul_a,0)-nvl(shul_b,0)),2)) danj from (select huoh,decode(substr(zhudcwh,1,2),'00',shul,0) shul_a, decode(substr(zhudcwh,1,2),'00',zhixj * shul,0) jine_a, decode(substr(beidcwh,1,2),'00',shul,0) shul_b, decode(substr(beidcwh,1,2),'00',zhixj * shul,0) jine_b from xs.cangkmxlsz where (denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and caozlx = '01' and huoh like '00%') and ((zhudcwh like '00%' and (beidcwh between '010001' and '010020' or beidcwh > '010030')) or (beidcwh like '00%' and (zhudcwh between '010001' and '010020' or zhudcwh > '010030'))) ) group by huoh
round(sum(nvl(a.jine,0)-nvl(b.jine,0)),2) jine,
decode(sum(nvl(a.shul,0)-nvl(b.shul,0)),0,0,round(sum(nvl(a.jine,0)-nvl(b.jine,0))/sum(nvl(a.shul,0)-nvl(b.shul,0)),2)) danj
from (select huoh ,nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine
from xs.cangkmxlsz
where zhudcwh like '00%' and
(beidcwh between '010001' and '010020' or beidcwh > '010030') and
denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and
to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and
caozlx = '01' and huoh like '00%' group by huoh) a,
( select huoh, nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine
from xs.cangkmxlsz
where beidcwh like '00%' and
(zhudcwh between '010001' and '010020' or zhudcwh > '010030') and
denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and
to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and
caozlx = '01' and huoh like '00%' group by huoh) b
where a.huoh=b.huoh(+)
group by a.huoh
union all
select b.huoh,round(sum(nvl(a.shul,0)-nvl(b.shul,0)),2) shul,
round(sum(nvl(a.jine,0)-nvl(b.jine,0)),2) jine,
decode(sum(nvl(a.shul,0)-nvl(b.shul,0)),0,0,round(sum(nvl(a.jine,0)-nvl(b.jine,0))/sum(nvl(a.shul,0)-nvl(b.shul,0)),2)) danj
from (select huoh ,nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine
from xs.cangkmxlsz
where zhudcwh like '00%' and
(beidcwh between '010001' and '010020' or beidcwh > '010030') and
denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and
to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and
caozlx = '01' and huoh like '00%' group by huoh) a,
( select huoh, nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine
from xs.cangkmxlsz
where beidcwh like '00%' and
(zhudcwh between '010001' and '010020' or zhudcwh > '010030') and
denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and
to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and
caozlx = '01' and huoh like '00%' group by huoh) b
where a.huoh(+)=b.huoh
group by b.huoh
其中一条语句大概有两千多行,一条有五十左右
round(sum(nvl(jine_a,0)-nvl(jine_b,0)),2) jine,
decode(sum(nvl(shul_a,0)-nvl(shul_b,0)),0,0,
round(sum(nvl(jine_a,0)-nvl(jine_b,0))
/sum(nvl(shul_a,0)-nvl(shul_b,0)),2)) danj
(select huoh,decode(substr(zhudcwh,1,2),'00',shul,0) shul_a,
decode(substr(zhudcwh,1,2),'00',zhixj * shul,0) jine_a,
decode(substr(beidcwh,1,2),'00',shul,0) shul_b,
decode(substr(beidcwh,1,2),'00',zhixj * shul,0) jine_b
from xs.cangkmxlsz
where (denglrq between to_date('2001.1.1', 'yyyy-mm-dd')
and to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05'
and caozlx = '01' and huoh like '00%')
and ((zhudcwh like '00%'
and (beidcwh between '010001' and '010020' or beidcwh > '010030'))
or
(beidcwh like '00%'
and (zhudcwh between '010001' and '010020' or zhudcwh > '010030')))
) group by huoh
round(sum(nvl(jine_a,0)-nvl(jine_b,0)),2) jine,
decode(sum(nvl(shul_a,0)-nvl(shul_b,0)),0,0,
round(sum(nvl(jine_a,0)-nvl(jine_b,0))
/sum(nvl(shul_a,0)-nvl(shul_b,0)),2)) danj
from
(select huoh,decode(substr(zhudcwh,1,2),'00',shul,0) shul_a,
decode(substr(zhudcwh,1,2),'00',zhixj * shul,0) jine_a,
decode(substr(beidcwh,1,2),'00',shul,0) shul_b,
decode(substr(beidcwh,1,2),'00',zhixj * shul,0) jine_b
from xs.cangkmxlsz
where (denglrq between to_date('2001.1.1', 'yyyy-mm-dd')
and to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05'
and caozlx = '01' and huoh like '00%')
and ((zhudcwh like '00%'
and (beidcwh between '010001' and '010020' or beidcwh > '010030'))
or
(beidcwh like '00%'
and (zhudcwh between '010001' and '010020' or zhudcwh > '010030')))
) group by huoh
decode (substr( zhudcwh,1,2),'00',shul,0) shul_a,
decode(substr(zhudcwh,1,2),'00',zhixj * shul,0) jine_a,
decode(substr(beidcwh,1,2),'00',shul,0) shul_b,
decode(substr(beidcwh,1,2),'00',zhixj * shul,0) jine_b
这四个条件不符合,取出的字段shul不仅与zhudcwh有关,而且与beidcwh有关,一个条件并不能取得正确的shul.
条件决定了
beidcwh like '00%'成立 zhudcwh 就不是‘00%'了,字符类型‘00100001' < '01'
beidcwh like '00%' and (zhudcwh between '010001' and '010020' or zhudcwh > '010030')
beidcwh与zhudcwh不存在谁能决定谁,他们相互独立
zhudcwh like '00%' and (beidcwh between '010021' and '010030')
根据一个条件如何区分他们?
结果很多记录,不好验证.
你可以导出来几个huoh放在一个测试表中试一下。如有问题再来,自己琢磨一下。