如何用MySQL查询精准计算销售与采购订单的毛利？

Absolutely, you can handle FIFO-based cost matching and profit calculation entirely in MySQL—no more slow PHP loops iterating through every order. Let's walk through how to build this, using your sample scenario to make it concrete.

Solution Overview #

The core idea is to use MySQL window functions to calculate cumulative purchase and sale quantities, then match each sale to the earliest available purchase batches (FIFO rule). We'll break this into manageable CTEs (Common Table Expressions) to keep the logic clear.

Step 1: Define Sample Tables & Data #

First, let's mirror your scenario with simplified table structures (adjust these to match your actual schema):

-- Purchase table (matches your purchase_order_products)
CREATE TABLE purchase_order_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    purchase_date DATE,
    quantity INT,
    cost DECIMAL(10,2)
);

-- Sales table (replace with your actual sales line items table)
CREATE TABLE sales_order_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    sale_date DATE,
    quantity INT,
    selling_price DECIMAL(10,2)
);

-- Insert your sample data
INSERT INTO purchase_order_products (product_id, purchase_date, quantity, cost)
VALUES
(1, '2024-01-01', 3, 10),
(1, '2024-01-02', 4, 9),
(1, '2024-01-03', 2, 11);

INSERT INTO sales_order_products (product_id, sale_date, quantity, selling_price)
VALUES
(1, '2024-01-01', 2, 12),
(1, '2024-01-02', 3, 12),
(1, '2024-01-03', 2, 12),
(1, '2024-01-03', 2, 12);

Step 2: Build the FIFO Calculation Query #

This query uses CTEs to layer the logic step by step:

WITH purchase_running AS (
    -- Calculate cumulative purchased quantity per product (sorted by purchase date)
    SELECT
        product_id,
        purchase_date,
        quantity AS purchase_qty,
        cost,
        SUM(quantity) OVER (
            PARTITION BY product_id 
            ORDER BY purchase_date, id
        ) AS cumulative_purchase
    FROM purchase_order_products
),
sales_running AS (
    -- Calculate cumulative sold quantity per product (sorted by sale date)
    SELECT
        id AS sale_id,
        product_id,
        sale_date,
        quantity AS sale_qty,
        selling_price,
        SUM(quantity) OVER (
            PARTITION BY product_id 
            ORDER BY sale_date, id
        ) AS cumulative_sale
    FROM sales_order_products
),
fifo_matching AS (
    -- Match each sale to the earliest available purchase batches
    SELECT
        sr.sale_id,
        sr.sale_date,
        sr.product_id,
        sr.sale_qty,
        sr.selling_price,
        pr.purchase_date,
        pr.cost,
        -- Calculate how many units from this purchase batch are used for the current sale
        LEAST(pr.cumulative_purchase, sr.cumulative_sale) 
        - GREATEST(
            COALESCE(LAG(pr.cumulative_purchase) OVER (
                PARTITION BY sr.product_id, sr.sale_id 
                ORDER BY pr.purchase_date
            ), 0),
            COALESCE(LAG(sr.cumulative_sale) OVER (
                PARTITION BY sr.product_id 
                ORDER BY sr.sale_date, sr.sale_id
            ), 0)
        ) AS matched_qty
    FROM sales_running sr
    JOIN purchase_running pr
        ON sr.product_id = pr.product_id
        -- Ensure purchase batch is available before/at the time of sale
        AND pr.cumulative_purchase > COALESCE(LAG(sr.cumulative_sale) OVER (
            PARTITION BY sr.product_id 
            ORDER BY sr.sale_date, sr.sale_id
        ), 0)
        AND sr.cumulative_sale > COALESCE(LAG(pr.cumulative_purchase) OVER (
            PARTITION BY sr.product_id, sr.sale_id 
            ORDER BY pr.purchase_date
        ), 0)
)
-- Final report: aggregate profit by sale date
SELECT
    sale_date,
    product_id,
    SUM(sale_qty) AS total_units_sold,
    SUM(matched_qty * cost) AS total_cost,
    SUM(matched_qty * selling_price) AS total_revenue,
    SUM(matched_qty * (selling_price - cost)) AS total_profit
FROM fifo_matching
WHERE matched_qty > 0 -- Filter out zero-quantity matches
GROUP BY sale_date, product_id
ORDER BY sale_date, product_id;

Step 3: Verify Results Against Your Scenario #

Running the query will return exactly the profit breakdown you requested:

• 2024-01-01: Total profit = 4 (2 units × (12-10))
• 2024-01-02: Total profit = 8 (1 unit × (12-10) + 2 units × (12-9))
• 2024-01-03: Total profit = 8 (2 units × (12-9) + 2 units × (12-11))

Performance Tips #

To handle large datasets efficiently:

• Add indexes to speed up window function calculations:

  CREATE INDEX idx_purchase_fifo ON purchase_order_products (product_id, purchase_date, id);
  CREATE INDEX idx_sales_fifo ON sales_order_products (product_id, sale_date, id);
  

• Ensure you're using MySQL 8.0 or newer (window functions are required for this approach).
• For extremely large tables, consider partitioning by product_id or date ranges.

内容的提问来源于stack exchange，提问作者Mohammad Hammadi