mysql语句优化总结

最新推荐文章于 2025-02-06 07:44:39 发布

原创最新推荐文章于 2025-02-06 07:44:39 发布 · 303 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #select

收录于

mysql 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一种SQL查询优化方法，通过使用CONCAT函数合并多个字段进行分组和筛选，以提高查询效率并简化代码结构。

优化总结

CONCAT(str1,str2,…) 的使用

原代码片：

SELECT
    customer_name,
    customer_no,
    certificate_no,
    company_no,
    company_name,
    amount
FROM
    tb_loan_companies_customer
WHERE
    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,
    amount
FROM
    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)
    )