select distinct '1' as HJ, '1' as PX, KCSW_YWRQ, YFD_SHDID as CGSHMX_SHDID,
YFD_GYSID as CGSH_GYSID, YFD_CGYID as CGSH_CGYID, YFD_CKID as CGSH_CKID,
YFD_BMID as CGSH_BMID, YFD_LYLX as CGSH_SWLXID, YFDMX_WLID as CGSHMX_WLID,
WLXX_MC, WLXX_GG, WLXX_JLDW, WLXX_LBID, YFDMX_CGSL as CGSHMX_SSSL,
YFDMX_HSJG as CGSHMX_HSJG, YFDMX_HSJE as CGSHMX_HSJE, YFDMX_WSJG as CGSHMX_WSJG, YFDMX_WSJE as CGSHMX_WSJE,
nvl(FPSL,0) as YFDMX_FPSL, nvl(FPJE,0) as YFDMX_FPJE,
(nvl(FPSL,0) - nvl(JSSL,0)) as TZSL, (nvl(FPJE,0) - nvl(JSJE,0) ) as TZJE,
(YFDMX_CGSL - nvl(FPSL,0)) as WDPSL, (YFDMX_WSJE - nvl(JSJE,0) ) as WDPJE,
CGSHMX_POID, KCSW_DJBH, GHFP_KPRQ, FP.CGJSMX_FPID as GHFP_FPID, YFD_YWRQ,
YFDMX_YFDID, YFDMX_YFDXH, nvl(JSSL,0) as YFDMX_JSSL, nvl(JSJE,0) as YFDMX_JSJE
from YFDMX
left join YFD F on F.YFD_YFDID = YFDMX_YFDID
left join SWLX on SWLX_SWLXID = YFD_LYLX
left join KCSW on KCSW_DJBH = YFDMX_KCBH and KCSW_DJXH = YFDMX_KCXH
left join CGSHMX on CGSHMX_SHDID = KCSW_TZDBH and CGSHMX_XH = KCSW_TZDXH
left join CGSH on CGSH_SHDID = CGSHMX_SHDID
left join WLXX on WLXX_WLID = YFDMX_WLID
left join ( select max(CGJSMX_FPID) as CGJSMX_FPID, max(GHFP_KPRQ) as GHFP_KPRQ, CGJSMX_YFDID, CGJSMX_YFDXH,
sum(nvl(CGJSMX_FPSL,0)) as FPSL, sum(nvl(CGJSMX_WSJE,0)) as FPJE,
sum(nvl(CGJSMX_JSSL,0)) as JSSL, sum(nvl(CGJSMX_WSJSJE,0)) as JSJE
from CGJSMX
left join YFDMX on YFDMX_YFDID = CGJSMX_YFDID and YFDMX_YFDXH = CGJSMX_YFDXH
left join WLXX on WLXX_WLID = YFDMX_WLID
left join YFD G on YFD_YFDID = YFDMX_YFDID
left join GHFP on GHFP_FLID = CGJSMX_FLID and GHFP_FPID = CGJSMX_FPID
where nvl(CGJSMX_YFDID,' ')<>' ' and GHFP_ZT = 'F'
group by CGJSMX_YFDID, CGJSMX_YFDXH
) FP on FP.CGJSMX_YFDID = YFDMX_YFDID and FP.CGJSMX_YFDXH = YFDMX_YFDXH
where 1=1 and (SWLX_XTSWLXID = 'WGRK' or SWLX_XTSWLXID = 'WXRK')
第一优化数据库
第二,根据业务需求,对过程优化找DBA帮你分析吧
还有索引我就知道这么多了
1.将条件语句包含的字段做索引
如:
from YFDMX
left join YFD F on F.YFD_YFDID = YFDMX_YFDID
中 YFD.YFD_YFDID 和 YFDMX.YFDMX_YFDID 这2个字段都需要建立单独索引2.join的时候,数据量少的表或查询放在前面
3.尽量避免使用 distinct ,like等,因为很少会利用到索引
where mid(YFD_YFDID,1,2) = mid(YFDMX_YFDID ,1,2)
有点像oracle,用plsql分析一下,可以看到占用时间最多的部分,进行有针对性的优化
当然,必要的索引啥的肯定是必不可少的
(jiajiajing6)
同意你的看法的,当然数据时大了,就会出现“冗余”的情况的