优化以下mysql语句：
SELECTr.refund_id,r.company_uid,m.member_mobile,mb.company_name,mb.area_info,mb.address,(SELECTcount(refund_id)FROMvc_refundreturnWHEREbuyer_id = r.buyer_idAND seller_state = 2AND refund_state = 3AND refund_type IN (1, 3)LIMIT 1) AS refunded_count,(SELECTcount(refund_id)FROMvc_refundreturnWHEREbuyer_id = r.buyer_idAND seller_state = 2AND refund_type IN (2, 3)LIMIT 1) AS refundreturn_count,(SELECTcount(refund_id)FROMvc_refundreturnWHEREbuyer_id = r.buyer_idAND seller_state = 2AND refund_state = 3AND refund_type IN (2, 3)LIMIT 1) AS refundreturned_count,(SELECTcount(refund_id)FROMvc_refundreturnWHEREbuyer_id = r.buyer_idAND seller_state = 2AND refund_state < 3AND refund_type IN (2, 3)LIMIT 1) AS refundreturning_count
FROM`vc_refundreturn` `r`
LEFT JOIN `vc_member` `m` ON `r`.`buyer_id` = `m`.`member_id`
LEFT JOIN `vc_mbperfect` `mb` ON `r`.`buyer_id` = `mb`.`member_id`
WHERE(1AND (r.area_id IN (3036, 3037)OR (r.area_id IN (3159, 3160)AND r.salesman_id = 13767)))
GROUP BY`r`.`buyer_id`
ORDER BY`refund_id` DESC
LIMIT 0,15
优化后：
SELECTr.refund_id,r.company_uid,m.member_mobile,mb.company_name,mb.area_info,mb.address,COUNT(CASEWHEN r.seller_state = 2AND r.refund_state = 3AND r.refund_type IN (1, 3) THENr.refund_idEND) AS refunded_count,COUNT(CASEWHEN r.seller_state = 2AND r.refund_type IN (2, 3) THENr.refund_idEND) AS refundreturn_count,COUNT(CASEWHEN r.seller_state = 2AND r.refund_state = 3AND r.refund_type IN (2, 3) THENr.refund_idEND) AS refundreturned_count,COUNT(CASEWHEN r.seller_state = 2AND r.refund_state < 3AND r.refund_type IN (2, 3) THENr.refund_idEND) AS refundreturning_count
FROM`vc_refundreturn` AS r
LEFT JOIN `vc_member` AS m ON r.buyer_id = m.member_id
LEFT JOIN `vc_mbperfect` AS mb ON r.buyer_id = mb.member_id
WHERE(r.area_id IN (3036, 3037)OR (r.area_id IN (3159, 3160)AND r.salesman_id = 13767))
GROUP BYr.buyer_id
ORDER BYr.refund_id DESC
LIMIT 0,15;
此查询已对子查询进行了优化，将其替换为聚合函数和CASE语句。同时，WHERE子句和JOIN条件进行了调整，以提高查询效率。最终结果按refund_id降序排列，并限制结果集的范围为前15条记录。
本文链接：https://my.lmcjl.com/post/12545.html
展开阅读全文
4 评论