如何用MySQL JOIN关联销售与采购数据并计算利润率？

需要将products_purchased表中的price_bought字段关联到sales表，并在sales表中计算合理的利润率。

销售表（SALES） #

采购产品表（PRODUCTS_PURCHASED） #

期望输出 #

解决方案 #

观察期望输出的成本匹配逻辑，这里采用**先进先出（FIFO）**原则匹配采购成本，即优先使用最早采购的库存成本。以下是SQL实现方案：

步骤1：生成采购/销售累计数量 #

先给采购、销售数据按产品和日期计算累计数量，方便后续匹配成本：

WITH purchase_cte AS (
    SELECT
        dt,
        product,
        price_bought,
        -- 按产品分组，按日期排序计算累计采购量
        SUM(qty) OVER (PARTITION BY product ORDER BY dt) AS cumulative_purchased
    FROM (
        -- 若采购表有批量采购，此处替换为实际采购数量字段
        SELECT dt, product, price_bought, 1 AS qty
        FROM products_purchased
    ) p
),
sales_cte AS (
    SELECT
        dt,
        product,
        qty,
        total,
        -- 按产品分组，按日期排序计算累计销售量
        SUM(qty) OVER (PARTITION BY product ORDER BY dt) AS cumulative_sold,
        -- 标记当前销售记录的累计数量范围
        SUM(qty) OVER (PARTITION BY product ORDER BY dt) - qty + 1 AS sold_start,
        SUM(qty) OVER (PARTITION BY product ORDER BY dt) AS sold_end
    FROM sales
)

步骤2：关联匹配成本并计算利润率 #

通过累计数量范围关联采购和销售数据，匹配对应成本后计算利润率：

SELECT
    s.dt,
    s.product,
    s.qty,
    s.total,
    p.price_bought,
    -- 利润率公式：(总销售额-总成本)/总销售额*100，保留两位小数
    ROUND((s.total - (s.qty * p.price_bought)) / s.total * 100, 2) AS profit_margin
FROM sales_cte s
JOIN purchase_cte p
    ON s.product = p.product
    AND p.cumulative_purchased BETWEEN s.sold_start AND s.sold_end
ORDER BY s.dt, s.product;

注意事项 #

• 若采购表存在单条记录对应多单位的批量采购，需将采购CTE中的qty替换为实际采购数量字段。
• 利润率计算逻辑可根据业务需求调整，比如改为基于单位利润的计算方式。

内容的提问来源于stack exchange，提问作者Fausto Pucheta Fortin