refactor(database): 优化客户数据查询的SQL性能

云

9 小时以前 ae46d524bb2675179e2c0ae4dd0e61d9d824ebca

refactor(database): 优化客户数据查询的SQL性能

- 重构销售台账收款金额统计查询，使用子查询避免重复计算
- 优化发票金额统计逻辑，提高查询效率
- 改进逾期款项统计的子查询结构
- 统一收款记录关联方式，使用join替代部分left join
- 简化多处重复的项目ID分组逻辑
- 提升客户维度数据汇总的查询性能

 src/main/resources/mapper/basic/CustomerMapper.xml

@@ -139,15 +139,16 @@
        from (select IFNULL(project_id, -1) as project_id, sum(contract_amount) as contractAmounts from sales_ledger group by IFNULL(project_id, -1)) T1
        left join project_management_info p on T1.project_id = p.id
        left join (
            select
                IFNULL(sl.project_id, -1) as project_id,
                sum(ascc.collection_amount) as receiptPaymentAmount
            select x.project_id, sum(x.collection_amount) as receiptPaymentAmount
            from (
                select distinct ascc.id, ascc.collection_amount, IFNULL(sl.project_id, -1) as project_id
            from account_sales_collection ascc
            left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
            WHERE sor.record_type='13' and sor.approval_status=1
            group by IFNULL(sl.project_id, -1)
                join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                join shipping_info s on sor.record_id = s.id
                join sales_ledger sl on s.sales_ledger_id = sl.id
                where sor.record_type='13' and sor.approval_status=1
            ) x
            group by x.project_id
        ) T2 on T1.project_id = T2.project_id
        left join (
            select
@@ -160,16 +161,17 @@
            group by IFNULL(sl.project_id, -1)
        ) T4 on T4.project_id = T1.project_id
        left join (
            select 
                IFNULL(sl.project_id, -1) as project_id,
                sum(asi.tax_inclusive_price) as invoiceAmount
            select x.project_id, sum(x.tax_inclusive_price) as invoiceAmount
            from (
                select distinct asi.id, asi.tax_inclusive_price, IFNULL(sl.project_id, -1) as project_id
            from account_sales_invoice asi
            left join account_invoice_application aia on asi.account_invoice_application_id = aia.id
            left join stock_out_record sor on FIND_IN_SET(sor.id, aia.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            left join sales_ledger sl on s.sales_ledger_id = sl.id
            where asi.status=0
            group by IFNULL(sl.project_id, -1)
            ) x
            group by x.project_id
        ) T5 on T5.project_id = T1.project_id
        left join (
            select IFNULL(sl.project_id, -1) as project_id,
@@ -178,13 +180,16 @@
                   sum(case when DATEDIFF(NOW(), sl.execution_date) > 30 and IFNULL(T1_rcpt.receiptPaymentAmount, 0) < sl.contract_amount then 1 else 0 end) as overdueCount
            from sales_ledger sl
            left join (
                select sl_in.id, sum(ascc.collection_amount) as receiptPaymentAmount
                select x.ledger_id as id, sum(x.collection_amount) as receiptPaymentAmount
                from (
                    select distinct ascc.id, ascc.collection_amount, sl_in.id as ledger_id
                from account_sales_collection ascc
                left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                left join shipping_info s on sor.record_id = s.id
                LEFT JOIN sales_ledger sl_in ON s.sales_ledger_id = sl_in.id
                WHERE sor.record_type='13' and sor.approval_status=1
                group by sl_in.id
                    join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                    join shipping_info s on sor.record_id = s.id
                    join sales_ledger sl_in on s.sales_ledger_id = sl_in.id
                    where sor.record_type='13' and sor.approval_status=1
                ) x
                group by x.ledger_id
            ) T1_rcpt on T1_rcpt.id = sl.id
            group by IFNULL(sl.project_id, -1)
        ) T6 on T6.project_id = T1.project_id
@@ -232,15 +237,17 @@
            group by customer_id, IFNULL(project_id, -1)
        ) T1
        left join (
            select
                sl.customer_id, IFNULL(sl.project_id, -1) as project_id,
                sum(ascc.collection_amount) as receiptPaymentAmount
            select x.customer_id, x.project_id, sum(x.collection_amount) as receiptPaymentAmount
            from (
                select distinct ascc.id, ascc.collection_amount,
                       sl.customer_id, IFNULL(sl.project_id, -1) as project_id
            from account_sales_collection ascc
            left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
            WHERE sor.record_type='13' and sor.approval_status=1
            group by sl.customer_id, IFNULL(sl.project_id, -1)
                join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                join shipping_info s on sor.record_id = s.id
                join sales_ledger sl on s.sales_ledger_id = sl.id
                where sor.record_type='13' and sor.approval_status=1
            ) x
            group by x.customer_id, x.project_id
        ) T2 on T1.customer_id = T2.customer_id and T1.project_id = T2.project_id
        left join (
            select
@@ -253,16 +260,18 @@
            group by sl.customer_id, IFNULL(sl.project_id, -1)
        ) T4 on T4.customer_id = T1.customer_id and T4.project_id = T1.project_id
        left join (
            select 
                sl.customer_id, IFNULL(sl.project_id, -1) as project_id,
                sum(asi.tax_inclusive_price) as invoiceAmount
            select x.customer_id, x.project_id, sum(x.tax_inclusive_price) as invoiceAmount
            from (
                select distinct asi.id, asi.tax_inclusive_price,
                       sl.customer_id, IFNULL(sl.project_id, -1) as project_id
            from account_sales_invoice asi
            left join account_invoice_application aia on asi.account_invoice_application_id = aia.id
            left join stock_out_record sor on FIND_IN_SET(sor.id, aia.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            left join sales_ledger sl on s.sales_ledger_id = sl.id
            where asi.status=0
            group by sl.customer_id, IFNULL(sl.project_id, -1)
            ) x
            group by x.customer_id, x.project_id
        ) T5 on T5.customer_id = T1.customer_id and T5.project_id = T1.project_id
        left join (
            select sl.customer_id, IFNULL(sl.project_id, -1) as project_id,
@@ -270,13 +279,16 @@
                   sum(case when DATEDIFF(NOW(), sl.execution_date) > 30 and IFNULL(T1_rcpt.receiptPaymentAmount, 0) < sl.contract_amount then 1 else 0 end) as overdueCount
            from sales_ledger sl
            left join (
                select sl_in.id, sum(ascc.collection_amount) as receiptPaymentAmount
                select x.ledger_id as id, sum(x.collection_amount) as receiptPaymentAmount
                from (
                    select distinct ascc.id, ascc.collection_amount, sl_in.id as ledger_id
                from account_sales_collection ascc
                left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                left join shipping_info s on sor.record_id = s.id
                LEFT JOIN sales_ledger sl_in ON s.sales_ledger_id = sl_in.id
                WHERE sor.record_type='13' and sor.approval_status=1
                group by sl_in.id
                    join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                    join shipping_info s on sor.record_id = s.id
                    join sales_ledger sl_in on s.sales_ledger_id = sl_in.id
                    where sor.record_type='13' and sor.approval_status=1
                ) x
                group by x.ledger_id
            ) T1_rcpt on T1_rcpt.id = sl.id
            group by sl.customer_id, IFNULL(sl.project_id, -1)
        ) T6 on T6.customer_id = T1.customer_id and T6.project_id = T1.project_id
@@ -312,16 +324,16 @@
               END AS isPaymentTimeout
        from sales_ledger sl
        left join (
            select
                sl.id,
                sum(ascc.collection_amount) as receiptPaymentAmount
            select x.ledger_id as id, sum(x.collection_amount) as receiptPaymentAmount
            from (
                select distinct ascc.id, ascc.collection_amount, sl.id as ledger_id
            from account_sales_collection ascc
            left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
            WHERE sor.record_type='13'
              and sor.approval_status=1
            group by sl.id
                join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                join shipping_info s on sor.record_id = s.id
                join sales_ledger sl on s.sales_ledger_id = sl.id
                where sor.record_type='13' and sor.approval_status=1
            ) x
            group by x.ledger_id
        )T1 on T1.id = sl.id
        left join (
            select sl.id,
@@ -333,16 +345,17 @@
            group by sl.id
        )T3 on T3.id = sl.id
        left join (
            select 
                sl.id,
                sum(asi.tax_inclusive_price) as invoiceAmount
            select x.ledger_id as id, sum(x.tax_inclusive_price) as invoiceAmount
            from (
                select distinct asi.id, asi.tax_inclusive_price, sl.id as ledger_id
            from account_sales_invoice asi
            left join account_invoice_application aia on asi.account_invoice_application_id = aia.id
            left join stock_out_record sor on FIND_IN_SET(sor.id, aia.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            left join sales_ledger sl on s.sales_ledger_id = sl.id
            where asi.status=0
            group by sl.id
            ) x
            group by x.ledger_id
        ) T5 on T5.id = sl.id
        where sl.customer_id = #{customerId}
        <if test="projectId!=null">
@@ -362,13 +375,16 @@
               IFNULL(sum(case when DATEDIFF(NOW(), sl.execution_date) > 30 and IFNULL(rcpt.receiptPaymentAmount, 0) &lt; sl.contract_amount then 1 else 0 end), 0) as totalOverdueCount
        from sales_ledger sl
        left join (
            select sl_in.id, sum(ascc.collection_amount) as receiptPaymentAmount
            select x.ledger_id as id, sum(x.collection_amount) as receiptPaymentAmount
            from (
                select distinct ascc.id, ascc.collection_amount, sl_in.id as ledger_id
            from account_sales_collection ascc
            left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            LEFT JOIN sales_ledger sl_in ON s.sales_ledger_id = sl_in.id
            WHERE sor.record_type='13' and sor.approval_status=1
            group by sl_in.id
                join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                join shipping_info s on sor.record_id = s.id
                join sales_ledger sl_in on s.sales_ledger_id = sl_in.id
                where sor.record_type='13' and sor.approval_status=1
            ) x
            group by x.ledger_id
        ) rcpt on rcpt.id = sl.id
    </select>
</mapper>