供应商往来查询入库金额sql调整优化

zss

4 天以前 b071f196403edce6e53bb991232c5d50b184a5ee

供应商往来查询入库金额sql调整优化

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

@@ -68,7 +68,7 @@
            </if>
        </where>
    </select>
    

    <select id="supplierTransactions" resultType="com.ruoyi.purchase.vo.SupplierTransactionsVo">
        SELECT T1.supplier_id,
               sm.supplier_name,
@@ -86,12 +86,26 @@
                INNER JOIN purchase_ledger pl ON pl.id = slp.sales_ledger_id
                WHERE sir.approval_status = 1 AND sir.record_type = 7 AND slp.type = 2
                UNION ALL
                SELECT sir.stock_in_num * slp.tax_inclusive_unit_price AS inbound_amount, pl.supplier_id
                FROM stock_in_record sir
                INNER JOIN quality_inspect qi ON qi.id = sir.record_id
                INNER JOIN purchase_ledger pl ON pl.id = qi.purchase_ledger_id
                INNER JOIN sales_ledger_product slp ON slp.sales_ledger_id = pl.id AND slp.product_model_id = sir.product_model_id
                WHERE sir.approval_status = 1 AND sir.record_type = 10 AND slp.type = 2
                 SELECT
                   sir.stock_in_num * slp_agg.tax_inclusive_unit_price AS inbound_amount,
                   pl2.supplier_id
               FROM stock_in_record sir
                        INNER JOIN quality_inspect qi
                                   ON qi.id = sir.record_id
                        INNER JOIN purchase_ledger pl2
                                   ON pl2.id = qi.purchase_ledger_id
                        INNER JOIN (
                   SELECT
                       sales_ledger_id,
                       product_model_id,
                       MIN(tax_inclusive_unit_price) AS tax_inclusive_unit_price
                   FROM sales_ledger_product
                   WHERE type = 2
                   GROUP BY sales_ledger_id, product_model_id
               ) slp_agg
                                   ON slp_agg.sales_ledger_id = pl2.id
                                       AND slp_agg.product_model_id = sir.product_model_id
               WHERE sir.approval_status = 1 AND sir.record_type = 10
            ) t
            GROUP BY t.supplier_id
        ) T3 ON T3.supplier_id = T1.supplier_id
@@ -121,12 +135,26 @@
               INNER JOIN sales_ledger_product slp ON slp.id = sir.record_id
               WHERE sir.approval_status = 1 AND sir.record_type = 7 AND slp.type = 2
               UNION ALL
               SELECT sir.stock_in_num * slp.tax_inclusive_unit_price AS inbound_amount, slp.sales_ledger_id
               SELECT
                   sir.stock_in_num * slp_agg.tax_inclusive_unit_price AS inbound_amount,
                   slp_agg.sales_ledger_id
               FROM stock_in_record sir
               INNER JOIN quality_inspect qi ON qi.id = sir.record_id
               INNER JOIN purchase_ledger pl2 ON pl2.id = qi.purchase_ledger_id
               INNER JOIN sales_ledger_product slp ON slp.sales_ledger_id = pl2.id AND slp.product_model_id = sir.product_model_id
               WHERE sir.approval_status = 1 AND sir.record_type = 10 AND slp.type = 2
                        INNER JOIN quality_inspect qi
                                   ON qi.id = sir.record_id
                        INNER JOIN purchase_ledger pl2
                                   ON pl2.id = qi.purchase_ledger_id
                        INNER JOIN (
                   SELECT
                       sales_ledger_id,
                       product_model_id,
                       MIN(tax_inclusive_unit_price) AS tax_inclusive_unit_price
                   FROM sales_ledger_product
                   WHERE type = 2
                   GROUP BY sales_ledger_id, product_model_id
               ) slp_agg
                                   ON slp_agg.sales_ledger_id = pl2.id
                                       AND slp_agg.product_model_id = sir.product_model_id
               WHERE sir.approval_status = 1 AND sir.record_type = 10
           ) t
           GROUP BY t.sales_ledger_id
       ) T2 ON T2.sales_ledger_id = pl.id