MySQL 索引下推（LCP）

select left(lcp.managecom,4) branchCode, ld.name institutionName, labg.BranchAttr salesTeamCode, labg.Name salesTeamName, lcp.appntno policyHolder,lcp.insuredno insuredPerson,lcp.contno policyNumber,riskcode productCode, lcp.cvalidate effectiveDate,lcp.prem premium, (select codename from ldcode where codetype='salechnl' and code=lcp.salechnl) salesChannel, la.groupagentcode agentCode, la.name agentName, (select case when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道' when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道' when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道' when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道' when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道' when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道' when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道' when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道' when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道' when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道' when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道' when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道' when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道' when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道' end from lcappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only ) policyHolderSalesChannel, (select la.groupagentcode from lcappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only) policyHolderGroupCode, (select case when agentstate>='06' then '离职' else '在职' end from lcappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) policyHolderIsEmployed, (select case when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道' when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道' when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道' when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道' when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道' when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道' when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道' when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道' when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道' when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道' when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道' when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道' when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道' when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道' end from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only ) insuredPersonSalesChannel, (select groupagentcode from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only) insuredPersonGroupCode, (select case when agentstate>='06' then '离职' else '在职' end from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) insuredPersonIsEmployed from lcpol lcp, ldcom ld, LABranchGroup labg, laagent la where lcp.managecom = ld.comcode and la.agentcode=lcp.agentcode and labg.AgentGroup = lcp.AgentGroup and lcp.conttype='1' and lcp.appflag='1' and lcp.cvalidate>= '2025-06-01' and lcp.cvalidate <= '2025-06-11' and salechnl IN ('04','13','14','15') and exists (select 1 from lcappnt lc, laagent la WHERE lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.agentstate < '06' union select 1 from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.agentstate < '06') union select left(lcp.managecom,4) branchCode, ld.name institutionName, labg.BranchAttr salesTeamCode, labg.Name salesTeamName, lcp.appntno policyHolder,lcp.insuredno insuredPerson,lcp.contno policyNumber,riskcode productCode, lcp.cvalidate effectiveDate,lcp.prem premium, (select codename from ldcode where codetype='salechnl' and code=lcp.salechnl) salesChannel, la.groupagentcode agentCode, la.name agentName, (select case when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道' when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道' when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道' when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道' when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道' when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道' when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道' when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道' when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道' when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道' when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道' when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道' when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道' when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道' end from lbappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only ) policyHolderSalesChannel, (select la.groupagentcode from lbappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only) policyHolderGroupCode, (select case when agentstate>='06' then '离职' else '在职' end from lbappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) policyHolderIsEmployed, (select case when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道' when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道' when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道' when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道' when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道' when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道' when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道' when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道' when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道' when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道' when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道' when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道' when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道' when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道' end from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only ) insuredPersonSalesChannel, (select groupagentcode from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only) insuredPersonGroupCode, (select case when agentstate>='06' then '离职' else '在职' end from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) insuredPersonIsEmployed from lbpol lcp, ldcom ld, LABranchGroup labg, laagent la where lcp.managecom = ld.comcode and la.agentcode=lcp.agentcode and labg.AgentGroup = lcp.AgentGroup and lcp.conttype='1' and lcp.appflag in ('1','3') and lcp.cvalidate>= '2025-06-01' and lcp.cvalidate <= '2025-06-11' and salechnl IN ('04','13','14','15') and lcp.edorno not like 'xb%' and exists (select 1 from lbappnt lc, laagent la WHERE lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.agentstate < '06' union select 1 from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.agentstate < '06') 优化这个db2的sql