基于平均采购价的MySQL库存结存与利润计算及数据插入问题

需求说明 #

需要从源表a2向库存表a1插入数据，插入时需严格按以下公式自动计算字段：

• 结存数量 = 上期结存数量 + 入库数量 - 出库数量
• 结存单价（平均采购价）=（上期结存金额 + 入库金额）/（上期结存数量 + 入库数量）
• 结存金额 = 结存数量 × 结存单价
• 利润 = 出库数量 ×（出库单价 - 结存单价）

表结构与初始数据示例 #

库存表a1建表语句 #

CREATE TABLE a1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    goods_id INT NOT NULL,
    last_stock_qty DECIMAL(10,2) COMMENT '上期结存数量',
    last_stock_amt DECIMAL(10,2) COMMENT '上期结存金额',
    in_qty DECIMAL(10,2) COMMENT '入库数量',
    in_amt DECIMAL(10,2) COMMENT '入库金额',
    out_qty DECIMAL(10,2) COMMENT '出库数量',
    out_price DECIMAL(10,2) COMMENT '出库单价',
    stock_qty DECIMAL(10,2) COMMENT '结存数量',
    stock_price DECIMAL(10,2) COMMENT '结存单价',
    stock_amt DECIMAL(10,2) COMMENT '结存金额',
    profit DECIMAL(10,2) COMMENT '利润',
    operate_date DATE NOT NULL
);

源表a2建表语句 #

CREATE TABLE a2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    goods_id INT NOT NULL,
    in_qty DECIMAL(10,2) COMMENT '入库数量',
    in_amt DECIMAL(10,2) COMMENT '入库金额',
    out_qty DECIMAL(10,2) COMMENT '出库数量',
    out_price DECIMAL(10,2) COMMENT '出库单价',
    operate_date DATE NOT NULL
);

初始数据 #

a1初始结存数据（示例商品1的上期库存）：

INSERT INTO a1 (goods_id, last_stock_qty, last_stock_amt, in_qty, in_amt, out_qty, out_price, stock_qty, stock_price, stock_amt, profit, operate_date)
VALUES (1, 100, 1000.00, 0, 0, 0, 0, 100, 10.00, 1000.00, 0, '2024-05-01');

a2待插入业务数据：

INSERT INTO a2 (goods_id, in_qty, in_amt, out_qty, out_price, operate_date)
VALUES (1, 50, 550.00, 30, 15.00, '2024-05-02');

修正后的插入SQL #

单商品场景 #

WITH last_stock AS (
    SELECT 
        goods_id,
        stock_qty AS last_stock_qty,
        stock_amt AS last_stock_amt
    FROM a1
    WHERE goods_id = (SELECT goods_id FROM a2)
    ORDER BY operate_date DESC
    LIMIT 1
)
INSERT INTO a1 (
    goods_id,
    last_stock_qty,
    last_stock_amt,
    in_qty,
    in_amt,
    out_qty,
    out_price,
    stock_qty,
    stock_price,
    stock_amt,
    profit,
    operate_date
)
SELECT 
    a2.goods_id,
    ls.last_stock_qty,
    ls.last_stock_amt,
    a2.in_qty,
    a2.in_amt,
    a2.out_qty,
    a2.out_price,
    -- 计算结存数量
    ls.last_stock_qty + a2.in_qty - a2.out_qty AS stock_qty,
    -- 计算结存单价（避免除零错误）
    CASE 
        WHEN (ls.last_stock_qty + a2.in_qty) = 0 THEN 0
        ELSE (ls.last_stock_amt + a2.in_amt) / (ls.last_stock_qty + a2.in_qty)
    END AS stock_price,
    -- 计算结存金额
    (ls.last_stock_qty + a2.in_qty - a2.out_qty) * 
    CASE 
        WHEN (ls.last_stock_qty + a2.in_qty) = 0 THEN 0
        ELSE (ls.last_stock_amt + a2.in_amt) / (ls.last_stock_qty + a2.in_qty)
    END AS stock_amt,
    -- 计算利润
    a2.out_qty * (a2.out_price - 
        CASE 
            WHEN (ls.last_stock_qty + a2.in_qty) = 0 THEN 0
            ELSE (ls.last_stock_amt + a2.in_amt) / (ls.last_stock_qty + a2.in_qty)
        END) AS profit,
    a2.operate_date
FROM a2
JOIN last_stock ls ON a2.goods_id = ls.goods_id;

多商品批量插入场景 #

如果a2包含多个商品的业务记录，需用窗口函数按商品分组取最新结存：

WITH last_stock AS (
    SELECT 
        goods_id,
        stock_qty AS last_stock_qty,
        stock_amt AS last_stock_amt,
        ROW_NUMBER() OVER (PARTITION BY goods_id ORDER BY operate_date DESC) AS rn
    FROM a1
    WHERE goods_id IN (SELECT goods_id FROM a2)
)
INSERT INTO a1 (
    goods_id,
    last_stock_qty,
    last_stock_amt,
    in_qty,
    in_amt,
    out_qty,
    out_price,
    stock_qty,
    stock_price,
    stock_amt,
    profit,
    operate_date
)
SELECT 
    a2.goods_id,
    ls.last_stock_qty,
    ls.last_stock_amt,
    a2.in_qty,
    a2.in_amt,
    a2.out_qty,
    a2.out_price,
    ls.last_stock_qty + a2.in_qty - a2.out_qty AS stock_qty,
    CASE 
        WHEN (ls.last_stock_qty + a2.in_qty) = 0 THEN 0
        ELSE (ls.last_stock_amt + a2.in_amt) / (ls.last_stock_qty + a2.in_qty)
    END AS stock_price,
    (ls.last_stock_qty + a2.in_qty - a2.out_qty) * 
    CASE 
        WHEN (ls.last_stock_qty + a2.in_qty) = 0 THEN 0
        ELSE (ls.last_stock_amt + a2.in_amt) / (ls.last_stock_qty + a2.in_qty)
    END AS stock_amt,
    a2.out_qty * (a2.out_price - 
        CASE 
            WHEN (ls.last_stock_qty + a2.in_qty) = 0 THEN 0
            ELSE (ls.last_stock_amt + a2.in_amt) / (ls.last_stock_qty + a2.in_qty)
        END) AS profit,
    a2.operate_date
FROM a2
JOIN last_stock ls ON a2.goods_id = ls.goods_id AND ls.rn = 1;

关键修正点 #

1. 上期结存准确性：通过CTE按商品+操作日期倒序取最新结存，确保计算用的是当前业务记录对应的真实上期库存。
2. 除零防护：加入CASE判断避免当上期结存+入库数量为0时出现除零错误。
3. 计算逻辑一致性：严格遵循需求公式的计算顺序，用同一结存单价计算结存金额和利润，避免数值偏差。
4. 多商品兼容：批量场景用ROW_NUMBER()窗口函数实现按商品分组取最新结存，适配多商品批量插入需求。

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