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