SELECT ss.`id`, pp.`id` AS parent_id, pp.`name` AS parent_name, ss.`name` FROM `pms_assess` AS ss RIGHT JOIN (SELECT s.`id`, ps.`name`, ps.`value` FROM `pms_assess` AS s RIGHT JOIN ( SELECT `name`, `value` FROM `pms_product_assess` WHERE product_id = 1663780757367164929 AND type = '最终评估' AND `name` NOT IN ( '最终评估最高值', '最终评估最低值', '商品分类' )) AS ps ON s.`name` = ps.`name`) AS pp ON ss.`parent_id` = pp.`id` AND ss.`name` = pp.`value`;优化这条SQL
时间: 2023-07-13 16:21:28 浏览: 35
这条 SQL 查询语句可以优化的地方有:
1. 使用 INNER JOIN 替换 RIGHT JOIN,因为 INNER JOIN 的效率更高。
2. 避免在 JOIN 语句中使用子查询,可以将子查询的结果保存到临时表中,然后在 JOIN 语句中使用这个临时表。
3. 避免在 WHERE 子句中使用函数,因为函数会影响查询的性能。可以将函数应用到查询条件的右侧,而不是左侧。
下面是优化后的 SQL 查询语句:
```
SELECT ss.id, pp.id AS parent_id, pp.name AS parent_name, ss.name
FROM pms_assess AS ss
INNER JOIN (
SELECT s.id, ps.name, ps.value
FROM pms_assess AS s
INNER JOIN (
SELECT name, value
FROM pms_product_assess
WHERE product_id = 1663780757367164929
AND type = '最终评估'
AND name NOT IN ('最终评估最高值', '最终评估最低值', '商品分类')
) AS ps ON s.name = ps.name
) AS pp ON ss.parent_id = pp.id AND ss.name = pp.value;
```
相关问题
将下面这条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
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 IN ("0","1") GROUP BY fpd_pay_num
将下面这条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
简化后的 SQL 语句如下:
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 IN ("0", "1")
GROUP BY fpd_pay_num