如何在PHP+MySQL中计算库存中每个商品的平均单价

你的核心问题是误用了算术平均函数AVG()来计算库存单价，而实际需要的是加权平均单价——即当前剩余库存的总金额除以总数量。

错误原因 #

AVG(stockreceive_trans.price)只是对所有采购单价取算术平均值，完全忽略了每批采购的数量权重，也没有考虑调拨/出库后的剩余库存，自然得不到正确结果。

解决方案分两种场景： #

场景1：stockreceive_trans记录每批采购的剩余数量

如果你的采购记录表会同步更新剩余数量（比如调拨后直接修改该批次的quantity为剩余值，和你例子中的表格结构一致），直接用剩余批次的金额总和除以剩余数量总和即可：

SELECT 
    `inventory`.`name` AS `item_name`, 
    `items`.`brand`,
    ROUND(SUM(stockreceive_trans.quantity * stockreceive_trans.price) / SUM(stockreceive_trans.quantity), 2) AS avg_price,
    `categories`.`name` AS `category_name`,
    items.measure_unit,
    inventory.barcode,
    inventory.inv_quantity
FROM `inventory`
JOIN `items` ON inventory.barcode = items.barcode            
JOIN `categories` ON inventory.category_id = categories.id
JOIN `stockreceive_trans` ON items.id = stockreceive_trans.item_id
WHERE inventory.warehouse_id = '$warehouse_id'
GROUP BY items.id, inventory.name, items.brand, categories.name, items.measure_unit, inventory.barcode, inventory.inv_quantity

场景2：stockreceive_trans仅记录采购入库数量，需关联出库/调拨表

如果采购表只记录原始入库数据，剩余库存通过单独的出库表（比如stockissue_trans）计算，需要先算出总采购金额，减去出库对应的金额，再除以当前库存数量：

SELECT 
    inv.name AS item_name, 
    it.brand,
    ROUND(
        (SUM(srt.quantity * srt.price) - COALESCE(SUM(sit.issue_quantity * (SUM(srt.quantity * srt.price)/SUM(srt.quantity))), 0)) 
        / inv.inv_quantity, 
    2) AS avg_price,
    cat.name AS category_name,
    it.measure_unit,
    inv.barcode,
    inv.inv_quantity
FROM inventory inv
JOIN items it ON inv.barcode = it.barcode            
JOIN categories cat ON inv.category_id = cat.id
JOIN stockreceive_trans srt ON it.id = srt.item_id
LEFT JOIN stockissue_trans sit ON it.id = sit.item_id
WHERE inv.warehouse_id = '$warehouse_id'
GROUP BY inv.id, it.id, cat.id, inv.name, it.brand, cat.name, it.measure_unit, inv.barcode, inv.inv_quantity

关键逻辑说明：

• SUM(srt.quantity * srt.price)：计算该商品所有采购的总金额
• COALESCE(...)：处理无出库记录的情况，避免出现NULL值
• 出库金额按加权平均规则计算，用总采购金额减去出库金额得到当前库存总金额，再除以inv_quantity（当前库存数量）

补充说明

如果你的库存计价方式是先进先出（FIFO）（比如例子中优先调拨后入库的批次），SQL实现会更复杂，需要按采购时间排序，逐批扣减出库数量后计算剩余批次的金额总和，这种情况可以使用窗口函数或子查询来实现批次级的数量扣减。

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