mysql语句优化总结

优化总结

CONCAT(str1,str2,…) 的使用

原代码片：

SELECT    customer_name,    customer_no,    certificate_no,    company_no,    company_name,    amountFROM    tb_loan_companies_customerWHERE    customer_name IN(        SELECT            customer_name        FROM            tb_loan_companies_customer        GROUP BY            customer_name,            customer_no        HAVING            count(customer_name)> 1        ORDER BY            customer_name    )AND certificate_no IN(    SELECT        certificate_no    FROM        tb_loan_companies_customer    GROUP BY        customer_name,        certificate_no    HAVING        count(certificate_no)> 1    ORDER BY        customer_name)

经过优化的代码片：

SELECT    customer_name,    customer_no,    certificate_no,    company_no,    company_name,    amountFROM    tb_loan_companies_customer WHERE    CONCAT(customer_name,customer_no,certificate_no) IN(        SELECT CONCAT(customer_name,customer_no,certificate_no)         FROM tb_loan_companies_customer        GROUP BY            CONCAT(customer_name,customer_no,certificate_no)        HAVING            count(CONCAT(customer_name,customer_no,certificate_no))> 1        ORDER BY            CONCAT(customer_name,customer_no,certificate_no)    )