子查询优化替换为聚合函数和CASE语句案例

优化以下mysql语句：

SELECT
	r.refund_id,
	r.company_uid,
	m.member_mobile,
	mb.company_name,
	mb.area_info,
	mb.address,
	(
		SELECT
			count(refund_id)
		FROM
			vc_refundreturn
		WHERE
			buyer_id = r.buyer_id
		AND seller_state = 2
		AND refund_state = 3
		AND refund_type IN (1, 3)
		LIMIT 1
	) AS refunded_count,
	(
		SELECT
			count(refund_id)
		FROM
			vc_refundreturn
		WHERE
			buyer_id = r.buyer_id
		AND seller_state = 2
		AND refund_type IN (2, 3)
		LIMIT 1
	) AS refundreturn_count,
	(
		SELECT
			count(refund_id)
		FROM
			vc_refundreturn
		WHERE
			buyer_id = r.buyer_id
		AND seller_state = 2
		AND refund_state = 3
		AND refund_type IN (2, 3)
		LIMIT 1
	) AS refundreturned_count,
	(
		SELECT
			count(refund_id)
		FROM
			vc_refundreturn
		WHERE
			buyer_id = r.buyer_id
		AND seller_state = 2
		AND refund_state < 3
		AND 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
	(
		1
		AND (
			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

优化后：

SELECT
	r.refund_id,
	r.company_uid,
	m.member_mobile,
	mb.company_name,
	mb.area_info,
	mb.address,
	COUNT(
		CASE
		WHEN r.seller_state = 2
		AND r.refund_state = 3
		AND r.refund_type IN (1, 3) THEN
			r.refund_id
		END
	) AS refunded_count,
	COUNT(
		CASE
		WHEN r.seller_state = 2
		AND r.refund_type IN (2, 3) THEN
			r.refund_id
		END
	) AS refundreturn_count,
	COUNT(
		CASE
		WHEN r.seller_state = 2
		AND r.refund_state = 3
		AND r.refund_type IN (2, 3) THEN
			r.refund_id
		END
	) AS refundreturned_count,
	COUNT(
		CASE
		WHEN r.seller_state = 2
		AND r.refund_state < 3
		AND r.refund_type IN (2, 3) THEN
			r.refund_id
		END
	) 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 BY
	r.buyer_id
ORDER BY
	r.refund_id DESC
LIMIT 0,
 15;

此查询已对子查询进行了优化，将其替换为聚合函数和CASE语句。同时，WHERE子句和JOIN条件进行了调整，以提高查询效率。最终结果按refund_id降序排列，并限制结果集的范围为前15条记录。