mysqlselect优化sql执行计划
explain select *, cast(prorder.deliveryfeedback as char) as deliveryfeedback, cast(prorder.pocreatedate as char) as pocreatedate from ( select request.fnumber, request.purtaskid, rd.request_detail_id, rd.request_id, item.skuid, item.sku, item.purdesc, item.purspec, concat(request.purtaskid,'',rd.skuid) as combineid, rd.stopkg, rd.purpkg, rd.reqpurqty, rd.requestqty, rd.poqty, rd.piqty, rd.sponroadqty, rd.revshipqty, rd.podate, rd.pidate, rd.shipdate, rd.revshipdate, rd.skulabel, rd.reqdeliverydate, rd.purpkgid, request.fstatus, request.urgency, date_format(request.checktime, '%y-%m-%d %t') as checktime, sys_user.firstname, concat(sys_user.firstname,'',sys_user.lastname) as username, sysuser.email, date_format(request.create_date, '%y-%m-%d') createdate from rs_request_detail rd inner join rs_request request on request.request_id = rd.request_id left join bs_item item on rd.skuid = item.skuid left join sys_user sysuser on sysuser.userid = request.user_id left join sys_user sys_user on item.pmid = sys_user.userid where (1 = 1) and item.isvirtual=0 and request.purtaskid>0 and request.fstatus in(22,23,30) order by rd.request_id desc limit 1,5000 ) t left join ( select group_concat(distinct pod.delivery_feedback) deliveryfeedback, max(pod.delivery_feedback) deliveryfeedbackmax , po.createdate as pocreatedate, po.purtaskid, pod.skuid, pod.delivery_feedback_remark, 'cny' as curno, group_concat(distinct po.orderno) orderno, format(sum((select rate from exchange_rate where money_type = po.curno )*pod.price/ (select rate from exchange_rate where money_type = 'cny')*pod.purqty)/ sum(pod.purqty),2) as amount from pr_order po inner join pr_order_detail pod on po.prorderid = pod.prorderid and po.purtaskid > 0 where po.purtaskid > 0 group by po.purtaskid,pod.skuid order by null ) prorder on prorder.purtaskid=t.purtaskid and prorder.skuid=t.skuid left join ( select a.purtaskid, a.skuid, sum(a.quantity) detectquantity, sum(a.batchchecknum) batchchecknum, date_format(a.transdate,'%y-%m-%d %h:%i:%s') transdate, date_format(a.detectdate,'%y-%m-%d %h:%i:%s') detectdate from ( select prorder.prorderid, detect.detect_id, prorder.purtaskid, detect.skuid, detect.quantity, sum(detectdetail.batchchecknum) as batchchecknum, detect.transdate, ifnull(detectdetail.detectdate,date('9999-01-01')) as detectdate from pr_order prorder inner join scm_detect detect on prorder.prorderid = detect.prorderid and prorder.purtaskid>0 left join scm_detect_detail detectdetail on detectdetail.detect_id =detect.detect_id group by prorder.purtaskid,detect.skuid,detect.detect_id order by null ) a group by a.purtaskid,a.skuid order by null ) detectd on detectd.purtaskid=t.purtaskid and detectd.skuid=t.skuid order by t.request_id desc