优化之前:
SELECT
ap.institution_id,
date(ra.create_time) date,
sum(
IF (
ra.confirm_amount > ld.debit_amount,
ld.debit_amount,
ra.confirm_amount
)
) return_amount,
count(ra.confirm_amount) return_count
FROM
t_asset_package ap
JOIN (
SELECT
asset_package_id,
institution_id,
loan_document_id,
debit_amount
FROM
t_loan_details
WHERE
is_all_paid = 1
OR principal_paid = 1
) ld ON ap.asset_package_id = ld.asset_package_id
JOIN (
SELECT
loan_document_id,
sum(confirm_amount) confirm_amount,
create_time
FROM
t_return_application
GROUP BY
loan_document_id
) ra ON ld.loan_document_id = ra.loan_document_id
WHERE
ap.institution_id = 'C0000012413'
AND ap.delete_flag = 0
GROUP BY
date(ra.create_time)
ORDER BY
date;
执行计划:
+----+---------