The select t3. *, NVL (t5 sum_kcsl, 0) sum_kcsl, NVL (t5 sum_clsl, 0) sum_clsl
The from (select t2 fydm,
T2. MC,
T2. Gg,
T2 KFBH,
T2. PCH,
T2 SFKCCL,
T2. Dw,
T2. Jx,
T2. DJ,
The sum (t2. Sl) as ZSL,
The sum (t2. Je) as zje
The from zl_zydj t, fy_fymx t2
Where t2. JZXH=t.j ZXH
And t2. ZHBH=t.z HBH
And t2. ZXBZ='0'
And (t2) fylb='0' or t2) WZLB='1')
And t2. KFBH='103116'
And t2. ZHBH='3305230028'
And t2. Fyzt='0'
And t2. JZLB='1'
And t.z yzt & lt;> '4'
Group by t2 fydm,
T2. MC,
T2. Gg,
T2 KFBH,
T2. PCH,
T2 SFKCCL,
T2. Dw,
T2. Jx,
T2. DJ
) t3
Left the join (select t4 WZBH,
T4 KFBH,
T4. PCH,
The sum (t4) KCSL) as sum_kcsl,
The sum (t4) CLSL) as sum_clsl
The from kc_pckctz t4
Where 1=1
And t4. KFBH='103116'
And t4. ZXBZ='0'
And t4. Sfty='0'
And t4. ZHBH='3305230028'
And t4. Yxqz & gt;
='20170214'And (t4) KCSL & lt;> 0.0 the or t4. CLSL & lt;> 0.0)
Group by t4 WZBH, t4 KFBH, t4, t5 PCH)
PCH on t3. PCH=t5.
And t3. Fydm=t5. WZBH
And t3. KFBH=t5. KFBH
And ((decode (t3. PCH, null, 1, 0))=1 or
(t5. Sum_kcsl & lt;> 0 or t5. Sum_clsl & lt;> 0))



Below is the execution plan:

CodePudding user response:

1, zl_zydj table if there is a suitable index column
2, according to business needs, the appropriate reference sum over (partition by), than the group by so much faster

CodePudding user response:

And t2. ZHBH='3305230028' to
?
And t.z HBH='3305230028'
?