sql语句的优化

[cyc123007512]

请各位大虾帮忙优化下面这条sql语句，执行太慢了，搞了一天，头都大了还没搞好(索引已经建立)。
select distinct p.projectNo,p.projectName,p.projectDescription,p.consignSource,p.buildPlace,p.PONo,p.projectCooperateProtocol,
p.consignSourceDate,p.serviceMode,p.cooperateType,pt.productionTypeName,p.projectCost,p.projectQuantum,
ps.productionStateName,e.UserName,p.projectFoundDate,case p.isExamine when 0 then '无需审批' when 1 then '待审批' when 2 then '审批未通过'
when 3 then '审批已通过' end,ph.paymentHeadwayName,pst.projectScheduleTypeName,case p.isConfirmProjectDescription
when 1 then '待确认' when 2 then '已确认' when 3 then '无法确认工程量' end,pp.UserName1,pp.UserName2,pp.UserName3,pq.s1,pq.s2,pq.s3,pq.s4,pq.s5,p.infoFeedbackDate,psn.date2,psn.date3,psn.date4,psn.date5,psn.date6,psn.date7,psn.date8,psn.date9,psn.date10,psn.date11,psn.date12,psn.date13,psn.date14,psn.date15,psl.logNo,psl.epmsState,psl.epmsscheduleDate,psl.explainCause,psl.operationUserName,psl.operationDate,pwl.logNo,pwl.impelDate,pwl.linkmanInfo,pwl.result,pwl.cause,pwl.resolveMethod,pwl.operationUserName,pwl.operationDate,p.remarks,p.contractType,p.consignSourceDate,p.productionTypeID
from epms_projectInfo p,epms_productionType pt,epms_productionState ps,rl_employees e,epms_paymentHeadway ph,epms_projectScheduleType pst,
(select a.projectNo,e1.UserName UserName1,e2.UserName UserName2,e3.UserName UserName3 from epms_projectInfo a
left join epms_projectPrincipal b on a.projectNo=b.projectNo left join (select UserID,UserName from rl_employees) e1
on b.projectSuperintendent=e1.UserID left join (select UserID,UserName from rl_employees) e2 on b.hardwareSuperintendent=e2.UserID
left join (select UserID,UserName from rl_employees) e3 on b.softwareDebuggingSuperintendent=e3.UserID) pp,
(select p.projectNo,s1,s2,s3,s4,s5 from (select projectNo from epms_projectInfo) p
left join (select projectNo,qualityScore s1 from epms_projectQualityNote where projectQualityCheckDistinctionID=2 and projectQualityCheckItemID=1)pq1 on p.projectNo=pq1.projectNo
left join (select projectNo,qualityScore s2 from epms_projectQualityNote where projectQualityCheckDistinctionID=2 and projectQualityCheckItemID=2)pq2 on p.projectNo=pq2.projectNo
left join (select projectNo,qualityScore s3 from epms_projectQualityNote where projectQualityCheckDistinctionID=2 and projectQualityCheckItemID=3)pq3 on p.projectNo=pq3.projectNo
left join (select projectNo,qualityScore s4 from epms_projectQualityNote where projectQualityCheckDistinctionID=2 and projectQualityCheckItemID=4)pq4 on p.projectNo=pq4.projectNo
left join (select projectNo,qualityScore s5 from epms_projectQualityNote where projectQualityCheckDistinctionID=2 and projectQualityCheckItemID=5)pq5 on p.projectNo=pq5.projectNo
)pq,
(select p.projectNo,id1,date1,ndate1,id2,date2,ndate2,id3,date3,ndate3,id4,date4,ndate4,id5,date5,ndate5,id6,date6,ndate6,id7,date7,ndate7,id8,date8,ndate8,id9,date9,ndate9,id10,date10,ndate10,id11,date11,ndate11,id12,date12,ndate12,id13,date13,ndate13,id14,date14,ndate14,id15,date15,ndate15
from (select projectNo from epms_projectInfo) p
left join (select projectNo,projectScheduleTypeID id1,projectScheduleDate date1,projectScheduleNoteDate ndate1 from epms_projectScheduleNote where projectScheduleTypeID=1)p1 on p.projectNo=p1.projectNo
left join (select projectNo,projectScheduleTypeID id2,projectScheduleDate date2,projectScheduleNoteDate ndate2 from epms_projectScheduleNote where projectScheduleTypeID=2)p2 on p.projectNo=p2.projectNo
left join (select projectNo,projectScheduleTypeID id3,projectScheduleDate date3,projectScheduleNoteDate ndate3 from epms_projectScheduleNote where projectScheduleTypeID=3)p3 on p.projectNo=p3.projectNo
left join (select projectNo,projectScheduleTypeID id4,projectScheduleDate date4,projectScheduleNoteDate ndate4 from epms_projectScheduleNote where projectScheduleTypeID=4)p4 on p.projectNo=p4.projectNo
left join (select projectNo,projectScheduleTypeID id5,projectScheduleDate date5,projectScheduleNoteDate ndate5 from epms_projectScheduleNote where projectScheduleTypeID=5)p5 on p.projectNo=p5.projectNo
left join (select projectNo,projectScheduleTypeID id6,projectScheduleDate date6,projectScheduleNoteDate ndate6 from epms_projectScheduleNote where projectScheduleTypeID=6)p6 on p.projectNo=p6.projectNo
left join (select projectNo,projectScheduleTypeID id7,projectScheduleDate date7,projectScheduleNoteDate ndate7 from epms_projectScheduleNote where projectScheduleTypeID=7)p7 on p.projectNo=p7.projectNo
left join (select projectNo,projectScheduleTypeID id8,projectScheduleDate date8,projectScheduleNoteDate ndate8 from epms_projectScheduleNote where projectScheduleTypeID=8)p8 on p.projectNo=p8.projectNo
left join (select projectNo,projectScheduleTypeID id9,projectScheduleDate date9,projectScheduleNoteDate ndate9 from epms_projectScheduleNote where projectScheduleTypeID=9)p9 on p.projectNo=p9.projectNo
left join (select projectNo,projectScheduleTypeID id10,projectScheduleDate date10,projectScheduleNoteDate ndate10 from epms_projectScheduleNote where projectScheduleTypeID=10)p10 on p.projectNo=p10.projectNo
left join (select projectNo,projectScheduleTypeID id11,projectScheduleDate date11,projectScheduleNoteDate ndate11 from epms_projectScheduleNote where projectScheduleTypeID=11)p11 on p.projectNo=p11.projectNo
left join (select projectNo,projectScheduleTypeID id12,projectScheduleDate date12,projectScheduleNoteDate ndate12 from epms_projectScheduleNote where projectScheduleTypeID=12)p12 on p.projectNo=p12.projectNo
left join (select projectNo,projectScheduleTypeID id13,projectScheduleDate date13,projectScheduleNoteDate ndate13 from epms_projectScheduleNote where projectScheduleTypeID=13)p13 on p.projectNo=p13.projectNo
left join (select projectNo,projectScheduleTypeID id14,projectScheduleDate date14,projectScheduleNoteDate ndate14 from epms_projectScheduleNote where projectScheduleTypeID=14)p14 on p.projectNo=p14.projectNo
left join (select projectNo,projectScheduleTypeID id15,projectScheduleDate date15,projectScheduleNoteDate ndate15 from epms_projectScheduleNote where projectScheduleTypeID=15)p15 on p.projectNo=p15.projectNo
)psn,
(select p.projectNo,logNo,epmsState,epmsscheduleDate,explainCause,operationUserName,operationDate from (select projectNo from epms_projectInfo) p left join( select a.projectNo,logNo,epmsState,epmsscheduleDate,explainCause,UserName operationUserName,a.operationDate from epms_proScheduleLog a,rl_employees,(select projectNo,max(operationDate)operationDate from epms_proScheduleLog group by projectNo)b where a.projectNo=b.projectNo and a.operationDate=b.operationDate and operationUserID=UserID)psl on p.projectNo=psl.projectNo) psl,
(select p.projectNo,logNo,impelDate,linkmanInfo,result,cause,resolveMethod,operationUserName,operationDate from (select projectNo from epms_projectInfo) p left join( select a.projectNo,logNo,impelDate,linkmanInfo,result,cause,resolveMethod,UserName operationUserName,a.operationDate from epms_wordlog a,rl_employees,(select projectNo,max(operationDate)operationDate from epms_wordlog group by projectNo)b where a.projectNo=b.projectNo and a.operationDate=b.operationDate and operationUserID=UserID)pwl on p.projectNo=pwl.projectNo)pwl left join epms_projectQualityNote as z on pwl.projectNo=z.projectNo
where p.productionTypeID=pt.productionTypeID and p.projectStateID=ps.productionStateID and p.projectFoundUserID=e.UserID and p.paymentHeadwayID=ph.paymentHeadwayID and p.projectScheduleTypeID=pst.projectScheduleTypeID and p.projectNo=pp.projectNo and p.projectNo=pq.projectNo and p.projectNo=psn.projectNo and p.projectNo=psl.projectNo and p.projectNo=pwl.projectNo
order by p.consignSourceDate desc,p.productionTypeID

[我在深圳搬砖-Justin]

看到都晕 看不下去啊！

[yulinlover]

建议你把数据库压缩一下发上来，那样大家也有环境帮你优化，发这么一个无脑的语句，谁看的下去？光是准备环境就的多久啊？

[Andy__Huang]

看这么多代码,头都晕了

[dawugui]

请给出表结构,测试数据,相关算法和需要的结果.谢谢!

[GUIGUIT]

这么长啊，我有时间了再慢慢看。

[肥龙上天]

你写的Sql语句实在不敢恭维！

请给出表结构,测试数据,相关算法和需要的结果.谢谢!

[sdhdy]

友情帮顶！