如何为同一产品存储不同采购价以精准核算销售利润？

要实现精准按单个库存单元（你提到的index）核算利润，需要调整数据库结构，拆分采购批次和库存明细，替代原products表单一采购价的设计：

1. 表结构调整 #

保留原products表 #

仅存储产品基础信息，移除采购价字段：

CREATE TABLE products (
    goods_id INT PRIMARY KEY,
    goods_name VARCHAR(100) NOT NULL,
    sell_price DECIMAL(10,2) NOT NULL, -- 固定销售价
    -- 可添加其他产品基础字段：分类、规格等
);

新增purchase_batches采购批次表 #

记录每一批次的采购核心信息：

CREATE TABLE purchase_batches (
    batch_id INT PRIMARY KEY AUTO_INCREMENT,
    goods_id INT NOT NULL,
    purchase_price DECIMAL(10,2) NOT NULL, -- 该批次的采购单价
    quantity INT NOT NULL, -- 该批次采购数量
    purchase_date DATETIME NOT NULL,
    FOREIGN KEY (goods_id) REFERENCES products(goods_id)
);

新增inventory_items库存明细表 #

追踪到每一件具体库存（对应你说的index 1-10）：

CREATE TABLE inventory_items (
    item_id INT PRIMARY KEY, -- 对应你的index值，比如1-10
    goods_id INT NOT NULL,
    batch_id INT NOT NULL,
    status ENUM('in_stock', 'sold') NOT NULL DEFAULT 'in_stock',
    sell_date DATETIME NULL,
    FOREIGN KEY (goods_id) REFERENCES products(goods_id),
    FOREIGN KEY (batch_id) REFERENCES purchase_batches(batch_id)
);

2. 数据插入示例 #

针对你的GOODS_X采购场景：

• 插入产品基础记录：

INSERT INTO products (goods_id, goods_name, sell_price) VALUES (1, 'GOODS_X', 20.00);

• 插入三批采购记录：

INSERT INTO purchase_batches (goods_id, purchase_price, quantity, purchase_date)
VALUES 
(1, 10.00, 1, '2024-01-01'),
(1, 12.00, 3, '2024-01-05'),
(1, 15.00, 6, '2024-01-10');

• 插入10件库存明细（对应index 1-10）：

INSERT INTO inventory_items (item_id, goods_id, batch_id)
VALUES 
(1, 1, 1),
(2, 1, 2),
(3, 1, 2),
(4, 1, 2),
(5, 1, 3),
(6, 1, 3),
(7, 1, 3),
(8, 1, 3),
(9, 1, 3),
(10, 1, 3);

3. 利润核算查询 #

要查询指定item_id的利润，执行以下SQL：

SELECT
    ii.item_id,
    p.sell_price,
    pb.purchase_price,
    (p.sell_price - pb.purchase_price) AS single_profit
FROM inventory_items ii
JOIN products p ON ii.goods_id = p.goods_id
JOIN purchase_batches pb ON ii.batch_id = pb.batch_id
WHERE ii.item_id IN (1,2,3,4,5,6,7,8,9,10);

查询结果会直接返回每件库存对应的利润，完全匹配你需要的1件利润10.00、3件每件8.00、6件每件5.00的需求。

设计优势 #

• 完全规避平均采购价带来的利润核算误差
• 精准追踪每一件库存的来源批次和采购成本
• 支持后续扩展库存状态管理、销售记录关联等功能

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