将下面这条sql语句优化简短一些 :SELECT * FROM ( SELECT fpd.fpd_id AS fpdId, fpd.fpd_budget_project AS fpdBudgetProject, fpd.fpd_budget_project_id AS fpdBudgetProjectId, fpd.fpd_pf_name AS fpdPfName, fpd.fpd_pf_id AS fpdPfId, fpd.fpd_expend_subject AS fpdExpendSubject, fpd.fpd_expend_subject_id AS fpdExpendSubjectId, fpd.fpd_payee_name AS fpdPayeeName, fpd.fpd_payee_account AS fpdPayeeAccount, fpd.fpd_payee_opening_bank AS fpdPayeeOpeningBank, fpd.fpd_payee_opening_bank_num AS fpdPayeeOpeningBankNum, fpd.fpd_dept_pay_economics_classify_num AS fpdDeptPayEconomicsClassifyNum, fpd.fpd_dept_pay_economics_classify_name AS fpdDeptPayEconomicsClassifyName, fpd.fpd_applied_amount AS fpdAppliedAmount, fpd.fpd_capital_use AS fpdCapitalUse, fpd.fpd_postscript AS fpdPostscript, fpd.fpd_budget_item_name AS fpdBudgetItemName, fpd.fpd_budget_item_number AS fpdBudgetItemNumber, fpd.fpd_dr_name AS fpdDrName, dbec.dbec_parent_num AS dbecParentNum FROM financial_payment_details fpd LEFT JOIN dept_budget_economy_class dbec ON fpd.fpd_dept_pay_economics_classify_num = dbec.dbec_num WHERE fpd.fpd_is_tax = "0" UNION ALL SELECT fpd.fpd_id AS fpdId, fpd.fpd_budget_project AS fpdBudgetProject, fpd.fpd_budget_project_id AS fpdBudgetProjectId, fpd.fpd_pf_name AS fpdPfName, fpd.fpd_pf_id AS fpdPfId, fpd.fpd_expend_subject AS fpdExpendSubject, fpd.fpd_expend_subject_id AS fpdExpendSubjectId, fpd.fpd_payee_name AS fpdPayeeName, fpd.fpd_payee_account AS fpdPayeeAccount, fpd.fpd_payee_opening_bank AS fpdPayeeOpeningBank, fpd.fpd_payee_opening_bank_num AS fpdPayeeOpeningBankNum, fpd.fpd_dept_pay_economics_classify_num AS fpdDeptPayEconomicsClassifyNum, fpd.fpd_dept_pay_economics_classify_name AS fpdDeptPayEconomicsClassifyName, sum( fpd.fpd_applied_amount ) AS fpdAppliedAmount, fpd.fpd_capital_use AS fpdCapitalUse, fpd.fpd_postscript AS fpdPostscript, fpd.fpd_budget_item_name AS fpdBudgetItemName, fpd.fpd_budget_item_number AS fpdBudgetItemNumber, fpd.fpd_dr_name AS fpdDrName, dbec.dbec_parent_num AS dbecParentNum FROM financial_payment_details fpd LEFT JOIN dept_budget_economy_class dbec ON fpd.fpd_dept_pay_economics_classify_num = dbec.dbec_num WHERE fpd.fpd_is_tax = "1" GROUP BY fpd_pay_num ) t1
时间: 2023-06-03 21:07:40 浏览: 159
如何优化sql语句[文].pdf
可以考虑将SELECT *改为只选取需要的列,尽量减少JOIN和UNION ALL操作的次数。另外可以考虑将UNION ALL改为UNION,使用GROUP BY时应尽量避免使用SELECT *,将需要聚合的列添加到GROUP BY语句中。最后,可以尝试优化索引以提高查询性能。
阅读全文